What are JOINS in SQL? (With Examples)

Understanding the different types of JOINS and how they work in SQL

JOIN is an essential feature in SQL, used to combine data from two or more tables based on at least one related column between them. You can use several columns for joining two tables in a single JOIN statement.

This article is based on T-SQL specifically. Which is the SQL flavour used in Microsoft’s relational database management system, SQL Server.

There are several types of JOINs in SQL, including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN and CROSS JOIN.

• INNER JOIN: Returns only the matching records between both tables.

• LEFT JOIN: Returns all records from the left table and matching records from the right table. If there are no matching rows in the right table, NULL values are returned.

• RIGHT JOIN: Returns all records from the right table and matching records from the left table. If there are no matching rows in the left table, NULL values are returned.

• FULL OUTER JOIN: Returns all records from both tables, matching records from both tables and NULL values where there is no match.

• CROSS JOIN: Returns the cartesian product of both tables, meaning that all possible combinations of rows from both tables are returned.

Now let us look at these JOINs in action.

For the following examples, I will be creating two simple tables with three records each. Below is the code I used to create these two tables. Feel free to copy the code and use it in SSMS to follow along with the examples to understand them better.

 -- create table1
CREATE TABLE table1 (
   id INT PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

-- insert data into table1
INSERT INTO table1 (id, name, age) VALUES
   (1, 'John', 25),
   (2, 'Jane', 30),
   (3, 'Bob', 22);

-- create table2
CREATE TABLE table2 (
   id INT PRIMARY KEY,
   salary INT,
   occupation VARCHAR(50)
);

-- insert data into table2
INSERT INTO table2 (id, salary, occupation) VALUES
   (1, 50000, 'Manager'),
   (2, 60000, 'Engineer'),
   (4, 40000, 'Technician');

These are the two tables when queried for all their columns:

SELECT *
FROM table1

SELECT *
FROM table2

Results for table1:

Results for table2

We will be joining these two tables on the ‘id’ column.

INNER JOIN Example:

-- perform INNER JOIN on table1 and table2
SELECT *
FROM table1
INNER JOIN table2 ON table1.id = table2.id;

Results for INNER JOIN:

As we can see, only rows for ‘id’ 1 and 2 were returned to us. This is because those two are matching records between the two tables.

LEFT JOIN Example:

-- perform LEFT JOIN on table1 and table2
SELECT *
FROM table1
LEFT JOIN table2 ON table1.id = table2.id;

Results for LEFT JOIN:

Now in this LEFT JOIN example, the table that is on the left side of the ‘LEFT JOIN’ keyword in the query is ‘table1’. This means that all rows for ‘table1’ will be returned to us. Matching rows from the right table (which is table2) will be returned as well but as you can see for id 3, there are no matching rows in ‘table2’. This means NULL values will be returned instead.

RIGHT JOIN Example:

-- perform RIGHT JOIN on table1 and table2
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.id = table2.id;

Results for RIGHT JOIN:

When we use the RIGHT JOIN, we can see the opposite has happened in comparison to the LEFT JOIN. The table on the right-hand side of the ‘RIGHT JOIN’ keyword in the query (table2) had all its rows returned. Then matching rows from the left-hand side table (table1) were returned and if there were no matching records for a row from table2 (such as id 4), then NULLS are returned.

FULL OUTER JOIN Example:

-- perform FULL OUTER JOIN on table1 and table2
SELECT *
FROM table1
FULL OUTER JOIN table2 ON table1.id = table2.id;

Results of FULL OUTER JOIN:

Using the FULL OUTER JOIN returns all rows to us even if the row doesn’t have a matching row in the other table. NULL values are returned where there is no match.

CROSS JOIN Example:

-- perform CROSS JOIN on table1 and table2
SELECT *
FROM table1
CROSS JOIN table2;

Results for CROSS JOIN:

CROSS JOIN returns a cartesian product of the tables. A cartesian product means that the resulting table will have all possible combinations of rows from ‘table1’ and ‘table2’. In this example, ‘table1’ has 3 rows and ‘table2’ also has 3 rows, the resulting table will have 9 rows (3 x 3).

It's important to note that a cartesian product can quickly generate a very large result set, so it should be used with caution. In most cases, you'll want to use a more specific type of join to join tables together.

Note: You don’t need to specify an ON condition in the CROSS JOIN in the query.

Hope this article helped you guys in understanding JOINS better. See you next Sunday for the next post!