Inner Join
🪢 “Give me only the rows that have matching values in both tables.”
This is best explained with a query example, so lets look at one...
Q. Display all the details of customers and orders.
S. To get all the details of customers and their orders we need to join the 2 tables and we will do that using inner join.
SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id;
Now this will return this big table:
| order_id | customer_id | item | price | status | id | name | age | address |
|---|---|---|---|---|---|---|---|---|
| OD1 | 1 | Mechanical Keyboard | 2000 | PENDING | 1 | Nexus | 22 | Cloud |
| OD2 | 1 | Noctua Fan | 1500 | PENDING | 1 | Nexus | 22 | Cloud |
| OD3 | 7 | Mechanical Keyboard | 2000 | PENDING | 7 | Gustav | NULL | Lumiere |
| OD4 | 5 | Mechanical Keyboard | 2000 | PENDING | 5 | Kratos | 1001 | Midgard |
| OD5 | 5 | Blades of Chaos | 9000 | SHIPPED | 5 | Kratos | 1001 | Midgard |
Now this is the result of an INNER JOIN ! It combined the two tables based on our condition and gave a resultant big table.
Now the magic happens in this expression: orders.customer_id = customers.id;
This here is our condition on which the inner join happens. Inner join says that I will return only those rows which appear in both the tables. In our case, We are seeing for records in orders.customer_id and customers.id, Any row where the customer_id field in orders table has the same value as the id field in customers table, inner join will return that row.
As we can see that our join point i.e. customer_id and id has the same values.
Even though our customers table has 7 records only 5 match our condition and we get a resultant table. Now we can perform other queries on this resultant table.
On thing to note that this table is temporary and Joins does not link the table at a schema level, they still are 2 independent tables. The result is temporary and is only for querying.
That is the gist of Inner Join.
Lets look at a few Questions to strengthen our grasp on inner join.
Q. Display the name of customers who have ordered a Mechanical Keyboard alongside show the status of their orders.
S. We can join the 2 tables on customer's id and then use the WHERE Clause to filter out what we need.
SELECT customers.name, orders.item, orders.status FROM orders INNER JOIN customers ON customers.id = orders.customer_id WHERE orders.item = 'Mechanical Keyboard';
Q. How many rows will this query return ?
SELECT customers.name, COUNT(orders.order_id) AS total_orders
FROM customers INNER JOIN orders ON customers.id = orders.customer_id GROUP BY customers.id;
S. This is a bit tricky one but lets go real slow.
First the join happens based on our condition i.e. customers.id = orders.customer_id, So it creates a joined table that has the same value for both fields. In our case its:
| customers.id | orders.customer_id | name of customer |
|---|---|---|
| 1 | 1 | "Nexus" |
| 1 | 1 | "Nexus" |
| 7 | 7 | "Gustav" |
| 5 | 5 | "Kratos" |
| 5 | 5 | "Kratos" |
Since "Nexus" and "Kratos" have 2 orders they appear twice in the orders table and hence inner join returns 2 of them as again the condition matched.
Now, A GROUP BY happens based on customers.id and as we know about group by it.....groups similar values into one so we go from 1, 1, 7, 5, 5 to 1, 7, 5.
| customers.id | orders.customer_id | name of customer |
|---|---|---|
| 1 | 1 | "Nexus" |
| 7 | 7 | "Gustav" |
| 5 | 5 | "Kratos" |
Next COUNT happens for each of these groups based on orders.order_id, So "Nexus" has 2 orders, "Kratos" has 2 and "Gustav" has 1 therefore the total orders per group is 2(Nexus), 2(Kratos) and 1(Gustav), we also show their names as name is also in SELECT.
Therefore it will return this table
| name | total_orders |
|---|---|
| Nexus | 2 |
| Kratos | 2 |
| Gustav | 1 |
And that answers our question - This query will return 3 rows;
That's it for INNER JOIN !
Follow Left Join to continue from here.
Browse Full SQL Page.