Nov 16, 2020
An Intro To SQL Joins
If you plan on learning SQL (or a programming language that incorporates SQL), then one of the best features will be Joining Tables. Below are the three main types of joins you will use and code examples for each.
How to write a join statement?
After your FROM statement you will put JOIN <table_name> ON <left_side_id> = <right_side_id>. You will see in the examples below what I mean.
Example Tables
Here are the two example tables we will be joining together today.
TABLE NAMED `suppliers`
supplier_id | supplier_name
============================
10000 | IBM
10001 | HP
10002 | Microsoft
10003 | IntelTABLE NAMED `ORDERS`
order_id | supplier_id | order_date
====================================
500 | 10000 | 2020-11-15
501 | 10002 | 2020-11-16
502 | 10010 | 2020-11-16
Left Join
A left join will return all records from the left table and only records that match on the right table.
SELECT
suppliers.supplier_id, supplier_name,
orders.order_date
FROM
suppliers
LEFT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;RESULT
supplier_id | supplier_name | order_date
=============================================
10000 | IBM | 2020-11-15
10001 | HP | null
10002 | Microsoft | 2020-11-16
10003 | Intel | null
Right Join
A right join will return all records from the right table and only records that match on the left table.
SELECT
suppliers.supplier_id, supplier_name,
orders.order_date, order_id
FROM
orders
RIGHT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;RESULT
supplier_id | supplier_name | order_date | order_id
=====================================================
10000 | IBM | 2020-11-15 | 500
10002 | Microsoft | 2020-11-16 | 501
null | null | 2020-11-16 | 502
Inner Join
An inner join will return all records that have an exact match on both the left side and the right side.
SELECT
suppliers.supplier_id, supplier_name,
orders.order_date, order_id
FROM
orders
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;RESULT
supplier_id | supplier_name | order_date | order_id
=====================================================
10000 | IBM | 2020-11-15 | 500
10002 | Microsoft | 2020-11-16 | 501
Additional Resources
