When using SQL JOINS within a query, you are combining rows from multiple tables into one result set based on the tables having a related column name.
The most common example of using SQL JOIN queries can be demonstrated by using a customers
table and an orders
table stored in the same database.
Let’s assume that the customers table looks like the following.
The order table could look like the below.
Each table contains a primary key column: customer_id
for the customers table, and order_id
for the orders table. Both tables have a related customer_id
column.
With this foreign key relation, you can use join queries to retrieve a set of information from both tables.
A basic JOIN query using the above tables might look like the below.
SELECT o.customer_id, c.first_name, c.last_name, o.order_total
FROM orders o
JOIN customers c
ON c.customer_id = o.customer_id;
This retrieves the following data.
By default, INNER JOIN
is the default type of JOIN if you don’t specify the type when you use JOIN
in the query.
This means that all records are retrieved that are common between both tables.
There is also a RIGHT JOIN
. This gets all records from the RIGHT linked table. If you have selected some columns from the LEFT table and there are no related records, these columns will contain NULL.
Using the example query above, as all of the data in both tables is linked (i.e. both customers added to the customers table have placed at least one order in the orders table), using RIGHT JOIN
will yield the same results as JOIN
and INNER JOIN
.
If however another customer was added to the customers table and they did not place an order, using the RIGHT JOIN
query will yield the below results.
SELECT o.customer_id, c.first_name, c.last_name, o.order_total
FROM orders o
RIGHT JOIN customers c
ON c.customer_id = o.customer_id;
Here the orders
table is the LEFT table (as it is selected first), and customers
is the RIGHT. Therefore all the records have been selected from the customers table even if the customers had no order records.
LEFT JOIN
performs the same functionality as LEFT except with information from the RIGHT linked table.
The FULL JOIN
gets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table.