SQL Join Types: Examples of Inner, Left, Right, and Full Joins

SQL Join Types: Examples of Inner, Left, Right, and Full Joins
Page content

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.

  1. Short Introduction to SQL
  2. DML Queries in SQL
  3. Aggregate and Scalar Functions in SQL
  4. Using Joins in SQL