Joins in SQL are used to select data from multiple tables based on a specific relationship. There are 4 SQL join types: Inner, Left, Right and Full. In this tutorial I will explain all of them with simple examples.
Introduction to SQL Join Types
Joins in SQL can be used to select data from two or more tables depending on a specific relationship between the data in the tables. Using joins is an easy way to simplify complicated queries.
An SQL table consists of columns of data. There is usually a primary key which is unique and is used to identify a particular tuple.
Here is the example data we will be using.
Table 1: customer
c_id name age city
1 pat 20 paris
2 bob 21 newark
3 jill 24 rio
4 sue 23 gaza
5 jim 25 paris
Table 2: transaction
t_id rec_no c_id
1 1111 1
2 2222 2
3 3333 3
4 4444 20
5 5555 25
There are these two tables that we will be using in the examples. One is the customer table which will contain customer details like customer id, name, age and city. The c_id is a primary key.
The 2nd table: transaction stores transaction details, t_id is the transaction id which is a primary key, rec_no is the receipt number, and c_id is the id of the customer involved in that transaction.
Now, there are four different types of joins that you can perform on these tables.
SQL INNER JOIN
The INNER JOIN can be used to return results where there is a match in both tables.
The syntax of INNER JOIN is
SELECT columnname
FROM tablename1 INNER JOIN tablename2
ON tablename1.columnname=tablename2.columnname
For example, if you do an INNER JOIN on these two example tables using the attribute c_id, it will list only those customers who have a transaction.
SELECT customer.name, transaction.rec_no
FROM customer INNER JOIN transaction
ON customer.c_id=transaction.c_id
This will print out the following result
name rec_no
pat 1111
bob 2222
jill 3333
These are only those customers who are involved in a transaction.
SQL LEFT JOIN
The LEFT JOIN returns all the rows from the left (first) table even if there are no matches in the right table.
The syntax of LEFT JOIN is
SELECT columnname
FROM tablename1 LEFT JOIN tablename2
ON tablename1.columnname=tablename2.columnname
For example, if you do a LEFT JOIN on these two example tables using the attribute c_id, it will list all the customers, even those who don’t have a transaction.
SELECT customer.name, transaction.rec_no
FROM customer LEFT JOIN transaction
ON customer.c_id=transaction.c_id
This will print out the following result
name rec_no
pat 1111
bob 2222
jill 3333
sue -
jim -
These are all the customers with their transactions if any.
SQL RIGHT JOIN
The RIGHT JOIN returns all the rows from the right (second) table even if there are no matches in the left table.
The syntax of RIGHT JOIN is
SELECT columnname
FROM tablename1 RIGHT JOIN tablename2
ON tablename1.columnname=tablename2.columnname
For example, if you do a RIGHT JOIN on these two example tables using the attribute c_id, it will list all the transactions, even those who don’t have a customer in the first table.
SELECT customer.name, transaction.rec_no
FROM customer RIGHT JOIN transaction
ON customer.c_id=transaction.c_id
This will print out the following result
name rec_no
pat 1111
bob 2222
jill 3333
- 4444
- 5555
These are all the transactions with their customers, if any.
SQL FULL JOIN
The FULL JOIN returns all the rows from both tables even if there are no corresponding matches for them in the other table.
The syntax of FULL JOIN is
SELECT columnname
FROM tablename1 FULL JOIN tablename2
ON tablename1.columnname=tablename2.columnname
For example, if you do a FULL JOIN on these two example tables using the attribute c_id, it will list all the customers and all the transactions even those without matches in the other table.
SELECT customer.name, transaction.rec_no
FROM customer FULL JOIN transaction
ON customer.c_id=transaction.c_id
This will print out the following result
name rec_no
pat 1111
bob 2222
jill 3333
sue -
jim -
- 4444
- 5555
These are all the customers/transactions with their corresponding transactions/customers, if any.
Thus, you can use Joins in SQL to extract data from multiple tables.
This post is part of the series: Introduction to SQL
This series is an introduction to SQL. It includes details on tables, DDL and DML queries, joins in SQL, and more.
