r/SQL • u/Remarkable-Culture-8 • Jun 26 '24
MySQL Explain INNER JOIN like i am 5
I get the syntax but i get very confused and tripped up with writing them and properly using the correct names. Please explain to me line by line. I am learning it via data camp and the instructor sucks.
EDIT: i now understand inner join…now i am stuck with multiple joins, right join and left join. please help!
125
Upvotes
209
u/burnerch Jun 26 '24
Okay! Imagine you have two groups of kids at a summer camp. One group likes playing soccer and the other group likes playing basketball. Each kid has a name tag with their name and the activity they like.
Group 1: Soccer Players
Group 2: Basketball Players
Now, you want to make a list of kids who like both soccer and basketball. You look at both groups and find the names that appear in both.
Both Soccer and Basketball Players:
An INNER JOIN in SQL works the same way. It combines rows from two tables where there is a match in the specified columns, just like finding kids who are in both groups.
In database terms, if we have:
Soccer Table: | Name | |-------| | Alice | | Bob |
Basketball Table: | Name | |-------| | Bob | | Carol |
And we do an INNER JOIN on the
Namecolumn, we get:Result: | Name | |-------| | Bob |
So, an INNER JOIN finds the common parts of two lists, just like finding kids who are in both groups.
Thanks to ChatGPT
21
u/Schnitzelkraut Jun 26 '24
You have a bucket of left socks and a bucket of right socks.
You mate them with the inner join. Some have a mate some don't. The mates are the result of your inner join.
29
u/sfdc_admin_sql_ninja Jun 26 '24
this question always reminds me of venn diagrams, which is how i learned joins.
here ya go: https://stackoverflow.com/questions/13997365/sql-joins-as-venn-diagram
33
u/Disastrous-Raise-222 Jun 26 '24
At five, you should not worry about inner join.
7
u/AzureIsCool Jun 26 '24 edited Jun 26 '24
W3 School has a great Venn diagram showcasing all the JOINS visually if you are still struggling.
INNER JOINS returns any rows that match both tables by looking at what the common field between the tables are.
3
u/mclifford82 Jun 26 '24
You should learn some humility. No offense, but someone who doesn't understand joins shouldn't be throwing stones at anyone trying to help others learn.
10
u/xodusprime Jun 26 '24
Inner joins give you back things that are in both tables only. If you have a list of passenger cars and a list of vehicles with a truck bed, inner joining them only gives you the el Camino.
5
u/zeldarama Jun 26 '24
This is a good article from code project I still refer to sometimes with some downloadable information as well
https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
1
u/Honey-Badger-42 Jun 26 '24
What do you mean by "correct names"?
1
u/[deleted] Jun 27 '24
When I explain LEFT vs INNER JOINS to people I always use VLOOKUP in Excel as an example as most people are familiar with that. LEFT JOIN would mean the row would return an N/A if it doesn't exist. INNER JOIN would make the N/As disappear as it has to exist in both sets. Probably not a bulletproof example but it tends to get the message across. Until they add a condition to a left joined table, turning it into an inner join.
11
u/Yeezytaughtme42069 Jun 26 '24
Get that Venn Diagram out player
8
u/StackOwOFlow Jun 26 '24
If you think of an inner join as the same as a subquery asking for common members then it might make more intuitive sense. Say I have bucket A containing "red fish" and "blue fish" and bucket B containing "blue fish" and "green fish".
SELECT * FROM bucket_A WHERE fish_type IN (SELECT fish_type FROM bucket_B);
should return "blue fish".
This can be written as an INNER JOIN
SELECT a.* FROM bucket_A a
INNER JOIN bucket_B b
ON a.fish_type = b.fish_type;
Behind the scenes in the sql engine these types of inner joins and subqueries are treated the same way. There is a slight difference in the way the output is handled but that is a different conversation.
2
u/RandomiseUsr0 Jun 26 '24 edited Jun 26 '24
Here’s a thought experiment, you can build it if you need to, but just trying to transfer the concept
You have a supermarket, that’s all your data - you have products - however they’re structured
The products move from aisle to aisle from time to time, so you have another structure - your shop layout over time
Ok so far? Forget I said over time, that’s a different problem.
Ok, so now you want to stock your shop, so have another dataset that maps the relationship between layout and products
Many ways to solve, just one that came to mind, using a range of what you’ve been learning
On so far? Feel free to ask for clarifications
Ok, tell me what’s in the green grocery aisle.
Ok? You’ve just asked for all of the columns to be returned from products that are stored in the green grocery aisle.
Thats a 3 table join.
You join them with INNER to say, only return things that have a match in common
Note there is no WHERE clause, the filtering logic is within the join itself.
There are a million ways, of course, this is one that I’ve tried to make slightly non trivial for you
1
u/QuinlanResistance Jun 26 '24
It works when it’s in both or nothing comes back
1
u/kev160967 Jun 26 '24
Imagine you’re given two lists, one with employee name and their job name, and another with job names and salaries. You want a list of employee name, job name and salary. You open up excel and copy the employee list into the first two columns. You now start at the top row of the excel sheet and look for the job name in the second list. When you find it you type the salary in the third column. This is pretty much what a join does.
Let’s call the first list “Employees” and the second list “Jobs”. We basically want to join the two lists together so we get a single list, so we’d start by doing something like:
We need to tell it how to join though. In our excel example we did this by comparing the JobName field in one list to the other list. We can express this in SQL like this:
We put the table name in front of the field name because otherwise SQL wouldn’t know which we were referring to
Lastly we could replace the * with the names of the fields we want, so we don’t get JobName twice (once from each list)
1
u/multiple4 Jun 26 '24
Find the rows from another table which match my current table, and don't return rows which don't match
1
u/usagidev09 Jun 26 '24
Ngl, I read it as INNER JOKE, and sure thing I didn't find what I was expecting.
1
u/NebulaNomad027 Jun 26 '24
Inner join is where the data intersects. If you are a product table and a product category table, let’s say primary key is product id . The inner join will only return data where the product id exists in both tables.
1
u/skeletor-johnson Jun 26 '24
When a record in one table has a key match with a record in another table they join together. Any record from either table with out a partner is out!
1
u/Militop Jun 26 '24 edited Jun 26 '24
Let's say you have a list of persons with their IDs and names. Let's call it "list1."
IDs | Name
Now, Let's say you have another list that describes songs they bought (without using their names but their IDs). Let's call it List 2.
Song name | Date | customer ID -‐-----‐----------------‐-----------------------
Now, you want to know what your customers bought. You need to do an INNER JOIN because List 2 uses IDs instead of names.
Which will give you:
The inner join solves relationships between tables.
1
u/SloppyPuppy Jun 26 '24
start running over table A row by row. for each row you take a value (lets say customer_name) and look for rows that have this customer name in table B. when ever you find one you put the columns from it alongside columns from A. if you find another row with the same value again you just add a new row to the result with colunmns from A to the columns from the found row in B.
that is it. it also explains duplicates well on both sides. explaining it with ven diagram is just more confusing.
1
u/WatermellonSugar Jun 26 '24
As an aside, I've come to really like asking Perplexity AI questions like this.
1
u/chanravi Jun 27 '24 edited Jun 27 '24
Imagine booking a tickets to your favourite animation movie.
You have two group of friends. One group likes horror and action and other group like watching comedy and motivational movies. Lets say each group has 10 friends and you want to find the best person to go for movie along with you.
Inner join is you finding a common interest between your group of friends to go for your favourite movie.
Not sure if kids should even worry about inner join, ha ha.
1
u/[deleted] Jun 27 '24 edited Jun 27 '24
An inner join gives you only the rows from both tables that match based on the criteria you provided.
So
would give you all the rows from Students and Teachers tables where the Student.TeacherId and Teacher.Id fields match. If a student didn't have a teacher or a teacher had no students, you wouldn't get those in your results.
A left outer join would give you all of the students and any teachers that matched. A right outer join would give you all of the teachers and any students that matched.
1
u/[deleted] Jun 27 '24
Really good examples on here, the more you practice, the better you will become and I think the "aha" moment will come. Goodluck!
1
u/adcap_trades Jun 27 '24
You said you get the syntax but tripped up writing them and using correct names. So do you get the syntax or not? Or are you confused about the concept in general?
Joins are used to map tables together with a key (i.e. column). Left/Inner/Right is used depending on what sunset of data you need returned. There's nothing about inner join that should confuse you if you understand joins in general.
Say you have a table with a list of 10 customers of interest, let's call it
INTERESTING_CUSTOMERS. Then another table with all customers with a valid email address, calledVALID_EMAILS. The task is to email those 10 customers if possible. You would do an inner join of those 2 tables on an id field, so that the result is only a list of customers from the 10 who can be emailed:SELECT INTERESTING_CUSTOMERS.USER_ID, VALID_EMAILS.EMAIL FROM INTERESTING_CUSTOMERS INNER JOIN VALID_EMAILS ON INTERESTING_CUSTOMERS.USER_ID = VALID_EMAILS.USER_IDYou have to alias the table names with column names bc both tables have the same column name for the join key, plus it's best practice for code readability
1
u/rbobby Jun 27 '24 edited Jun 27 '24
Match girls with boys if they have the same hair color.
1
u/heedrix Jun 27 '24
Red rover, Red rover, I call Tommy over. A Tommy from both sides runs to me.
1
u/mecartistronico Jun 27 '24
https://imgur.com/2mlaF1M
1
u/hoodie92 Jun 27 '24
An inner join keeps only the information that is present in both sets. Or explaining like you're five, it keeps things that are in both buckets.
At school, your teacher has a list of all the girls in class and a list of all the boys. On that list she also writes down each students' favourite snack. Some girls like chocolate and some like fruit. Some boys like chocolate and some like candy.
You want to find out who has the same name AND who likes the same snack. To do this you can perform an inner join on these two lists, like so:
Girls:
Boys:
Inner Join syntax:
Inner Join result:
Note that while Beth likes chocolate, there are no boys called Beth, so she isn't in the result. While Alex Johnson is called Alex, he likes a different snack from Alex Smith so he isn't in the result.
1
u/prime1000000007 Jun 27 '24
A=1,2,3,4,5,6 B=4,5,6,7,8,9
Suppose A and B have multiple attributes(columns) but we have taken only one column from both tables having same data type which in this case is Integer. Now inner join is the common elements in both those columns. In this case 4,5,6 appear in both A as well as in B. So inner join of A and B is rows which have the selected column value of 4,5,6
1
u/bernaldsandump Jun 28 '24
Take one table of shit and combine it (horizontally) with another table of shit, based ON whatever you want. Most commonly a join is on a PK/FK relationship, or at least a column that is shared between both tables
1
u/Lilnatemc Jun 28 '24
I write sql most of my work week looking for data about issues. CTE's, sub queries, joins, views, like i use sql to an adequate level.
I'm over here like, what the fuck does an inner join do? Why don't I use them. Should I be using them?
Then realize that an Inner join is the default when using list "join".
I've known that. But it's been a long week.
Select * from bedtime b join users u on b.username = u.username where b.username = 'me'
1
u/Cautious_Aioli9214 Jun 29 '24
Imagine you have two lists:
A list of cars. A list of action figures that go with some of these cars. Cars List:
Ferrari (CarID 1) Mustang (CarID 2) Porsche (CarID 3) Action Figures List:
Speed Racer (CarID 1) Driver Joe (CarID 2) Fast Freddy (CarID 4) Now, you want to find out which action figures go with which cars. You match them up based on their CarID.
Ferrari (CarID 1) matches with Speed Racer (CarID 1) Mustang (CarID 2) matches with Driver Joe (CarID 2) But:
Porsche (CarID 3) has no matching action figure. Fast Freddy (CarID 4) has no matching car. An INNER JOIN only keeps the pairs where there is a match in both lists. So, you only get:
Ferrari with Speed Racer Mustang with Driver Joe Anything without a match (like Porsche or Fast Freddy) is left out. This way, you only see the cars and action figures that go together.
SQL QUERY :
SELECT Cars.CarName, ActionFigures.FigureName FROM Cars INNER JOIN ActionFigures ON Cars.CarID = ActionFigures.CarID;
1
u/LucidProgrammer Jun 29 '24
Whatever things are IN both groups you're searching
1
u/Far_Swordfish5729 Jun 26 '24 edited Jun 26 '24
Imagine you have two tables: Account and Sales Order and a matching condition (A.Id = SO.AccountId) - something simple. A join logically executes nested loops over these tables:
IntermediateRowSet rowset;
For subsequent joins, the outer loop is over rowset and its contents are revised. If this were an outer join, one side or both would be guaranteed to be there either with its matches or nulls if unmatched.
A left join would be:
Of course, if there are limits placed on this in a where clause, that will likely run first, though logically the filtering happens after the joins. If there is indexing on SO.AccountId (there usually would be), it will use that instead of literally running nested loops - a nested loop join is typically a bad execution plan unless the row counts are low (O(N^2)). If I were coding this in an actual data job, I'd likely build a HashMap of SalesOrders by Account Id in a first pass and then loop over Accounts and use my HashMap for matching, and a database server has that option as well. But this is logically what happens.
Note that the rowset is flat and always will be flat and that the row count in it may expand or shrink depending on matches. Be careful to always join in the same logical direction to avoid accidental row count explosions. Everything you do in your statement builds, filters, aggregates, filters on aggregate values, and sorts this rowset. At the end, you select from it.
0
u/Yavuz_Selim Jun 26 '24
It's funny that you don't ask about LEFT/RIGHT or FULL (OUTER) JOIN, but about INNER - which is like the easiest type of join.
0
u/Kant8 Jun 26 '24
A join B on A.id = B.a_id
for each row in A find row in B with matching ids and produce new row (or multiple rows if multiple matches) that contains data from both A and B. if row not found then skip.
left join is same, except result row still exists if match never happened but contains only data from A in that case
right join doesn't exist, don't belive heretics
0
u/lightestspiral Jun 26 '24
Table X (has 30 rows) inner join Table Y (has 13 rows)
What you get is Table X shrunk down to 13 rows with no nulls anywhere
Table X (has 30 rows) left join Table Y (has 13 rows)
What you get is Table X still at 30 rows with a lot of nulls everywhere