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
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.
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
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;
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.
FULL JOIN gets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table.