The SQL JOIN Clause
The JOIN clause is used to combine rows from two or more tables, based on a related column between them, i.e., a primary key and foreign key relationship between the two tables.
JOIN Syntax
SQL joins are typically in the format:
SELECT STATEMENT
FROM TABLE A
JOIN CLAUSE TABLE A
ON clause;
The select statement is used to choose the exact columns one wants returned from each table being joined.
One can also can use select * to return all columns from both tables.
The join clause is where you specify the exact join operation you will be performing on the two tables.
The on condition is where the primary key in table A is linked to the foreign key in table B. This relation is what enables one to perform a join operation between two tables.
Types of Joins in SQL
- (INNER) JOIN
- LEFT (OUTER) JOIN
- RIGHT (OUTER) JOIN
- FULL (OUTER) JOIN
In order to demonstrate the different joins, I will create a shop schema called duka with three tables: products, customers and orders.
The link to the SQL code to create the schema is available here.
1. SQL INNER JOIN
The INNER JOIN returns only rows that have matching values in both tables.
Inner join syntax
SELECT columns
FROM table_a a
INNER JOIN table_b b
ON a.key = b.key
We can use inner join in our schema to figure out which customers placed which order.
In this case we will need the following columns:
- customers name (name)
- product id (product_id)
- quantity (quantity)
This data is found in the tables:
- duka_customers
- duka_orders
The common column between the two tables is:
- customer_id
Thus we can use inner join to return only the instances where a customer appears in the orders table, as follows:
select c.name, o.product_id, o.quantity
from duka.duka_customers c
inner join duka.duka_orders o
on c.customer_id = o.customer_id;
We get the following output showing each customers purchases:
One is not limited to one join clause, as long as a primary key - foreign key relationship exists between tables in a schema, you can use as many joins as needed.
We can further use a second inner join to pull the product name, to replace the product id, from the products table by joining it to the orders table.
The SQL query thus is modified to:
select c.name, p.product_name, o.quantity
from duka.duka_orders o
inner join duka.duka_customers c on c.customer_id = o.customer_id
inner join duka.duka_products p on p.product_id = o.product_id;
Our final output is a much more readable table showing the customer name, the product and the quantity of items they purchased.
2. LEFT JOIN
The LEFT JOIN returns all rows from the left table (table A), and only the matched rows from the right table (table B).
If there is no match in the right table, the result for the columns from the right table will be NULL.
Left join syntax
SELECT columns
FROM left_table a
LEFT JOIN right_table b
ON a.key = b.key
We can use left join to figure out which products have never been ordered.
In this case we need the columns :
- product name From the tables :
- products table
- orders table The common column is :
- product_id
The SQL query is written as:
select p.product_name
from duka.duka_products p
left join duka.duka_orders o
on p.product_id = o.product_id
where o.order_id is null;
We use the condition where o.order_id is null to filter only the rows where orders table is returning a null, showing no orders for that product exist.
Our output is therefore:
3. SQL RIGHT JOIN
The RIGHT JOIN returns all rows from the right table (table A), and only the matched rows from the left table (table B).
If there is no match in the left table, the result for the columns from the left table will be NULL.
Right join syntax
SELECT columns
FROM left_table a
RIGHT JOIN right_table b
ON a.key = b.key;
We can use a right join to figure out which products have never been ordered.
This time the SQL query will be:
select dp.product_name
from duka.duka_orders t
right join duka.duka_products dp
on t.product_id = dp.product_id
where t.order_id is null;
This time the order in which we have called the tables is swapped, but the output remains the same.
4. SQL FULL JOIN
The FULL JOIN returns all rows when there is a match in either the left or right table.
If a row in the left table has no match in the right table, the result set includes the left row's data and NULL values for all columns of the right table.
If a row in the right table has no match in the left table, the result set includes the right row's data and NULL values for all columns of the left table.
Full join syntax
SELECT columns
FROM table A
FULL JOIN table B
ON table_a.key= table_b.key
WHERE condition;
We can once gain use a full join to show the products that have never been ordered.
we will write the SQL query to illustrate this as follows:
select t.order_id, t.product_id, dp.product_name, dp.product_category, dp.stock_level, dp.supplier
from duka.duka_orders t
full join duka.duka_products dp
on t.product_id = dp.product_id;
We expect to see the order_id and product_id for products that were ordered since we are getting that data from the orders table. For products that were never ordered, those two columns will remain be null.
We get the following output:
Conclusion
summary
- (INNER) JOIN: Returns only rows that have matching values in both tables.
- LEFT (OUTER) JOIN: Returns all rows from the left table, and only the matched rows from the right table.
- RIGHT (OUTER) JOIN: Returns all rows from the right table, and only the matched rows from the left table.
- FULL (OUTER) JOIN: Returns all rows when there is a match in either the left or right table.
SQL joins can be visualized using the this image below, sourced from Acquity Training, which succinctly summarizes all the operation that we have performed on our duka database..
Understanding SQL joins is a key tool that one must master. These simple operations form the skills that one needs in order to work on larger, complex databases and develop as a developer and data analyst.
Good luck on your journey.
Top comments (0)