Quick Review of All Join Types & Effective Utilization to Optimize Query Speed
Executive Summary:
In this lesson, we’re going to review the most important component of SQL or Structured Query Language – The Join. Due to the magic of relational databases, they serve as the relation between multiple tables and allow for data to be combined between 1 or more tables. We’ll review the main types of joins in SQL, how they can be utilized, and how to optimize query speed.
Main Types of Joins:
- INNER JOIN
- OUTER JOIN
- RIGHT JOIN
- FULL JOIN
Build Example Database
-- Adding 10 rows to the Customers table
CREATE TABLE Customers (CustomerID INT, CustomerName Nvarchar(MAX), ContactName Nvarchar(MAX), Country Nvarchar(MAX) )
INSERT INTO Customers (CustomerID, CustomerName, ContactName, Country)
VALUES (1, 'John Doe', 'John Smith', 'United States'),
(2, 'Jane Smith', 'Jane Doe', 'Canada'),
(3, 'David Johnson', 'Emily Davis', 'United Kingdom'),
(4, 'Maria Garcia', 'Carlos Hernandez', 'Spain'),
(5, 'Hiroshi Tanaka', 'Yuko Suzuki', 'Japan'),
(6, 'Sophie Dupont', 'Luc Leclerc', 'France'),
(7, 'Luisa Rossi', 'Mario Bianchi', 'Italy'),
(8, 'Ana Silva', 'Pedro Sousa', 'Portugal'),
(9, 'Elena Petrova', 'Ivan Ivanov', 'Russia'),
(10, 'Sebastian Müller', 'Lena Wagner', 'Germany');
-- Adding 20 rows to the Orders table
CREATE TABLE Orders (OrderID INT, CustomerID INT, OrderDate DATE)
INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES (1, 1, '2023-01-01'), (2, 2, '2023-01-02'), (3, 3, '2023-01-03'),
(4, 4, '2023-01-04'), (5, 5, '2023-01-05'), (7, 7, '2023-01-07'),
(8, 8, '2023-01-08'), (9, 9, '2023-01-09'), (10, 10, '2023-01-10'),
(11, 1, '2023-01-11'), (12, 2, '2023-01-12'), (13, 3, '2023-01-13'),
(14, 4, '2023-01-14'), (15, 5, '2023-01-15'), (17, 7, '2023-01-17'),
(18, 8, '2023-01-18'), (19, 9, '2023-01-19'), (20, 10, '2023-01-20');
1. INNER JOIN
An Inner Join returns all matching records that have values in each table. We’ll be joining on CustomerID, so an inner join will only return all records relating to matching CustomerIDs. The below will return all customers with an order, that are in the United States.
SELECT
*
FROM
Customers
JOIN
Orders
ON Orders.CustomerID = Customers.CustomerID
WHERE
Customers.Country = 'United States'
2. LEFT JOIN
The second type of join is the Left Join or Left Outer Join. The type of join returns all records from the first, or left table, and all matching records from the second table. Where no records exist, a ‘null’ is returned.
SELECT Customers.CustomerName, Orders.OrderDate
FROM
Customers
LEFT JOIN
Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE
Orders.orderdate is null
OR Customers.Country = 'United States'
3. RIGHT JOIN
The Right Join returns all records when there is a match in either the left or the right table. If there is no match on both sides, null will be returned.
SELECT Customers.CustomerName, Orders.OrderDate
FROM
Orders
RIGHT JOIN
Customers
ON Customers.CustomerID = Orders.CustomerID
4. Full Join
A Full Join returns all records from both tables, when there is a match in either the left or right table. If there is no match, then the result is null.
SELECT *
FROM
Customers
FULL OUTER JOIN
Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE
Customers.Country = 'United States'
OR ORDERDATE IS NULL
Optimizing for Speed
Join operations can be resource intensive, especially as the number of records increases. There’s a few tricks to keep in mind to possibly assist in tweaking your code to ensure the best speed you can hope for.
- Avoid Unnecessary Columns
- Try to only work with the columns you require as extraneous columns can cause issues.
- Use Where Clauses Wisely
- Where clauses allow you to effectively parse down your result set. Try to parse your results as early as possible.
- Utilize Indexes
- Indexes can increase speed; They’re out of scope for this article but now you know they’re a thing that exists! Have fun learning!
- Minimize Outer Joins
- Outer joins can be some of the slowest joins utilized. Try to minimize their use and/or use them effectively.