Pin Me

Using Joins in SQL

written by: •edited by: Michele McDonough•updated: 7/16/2010

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.

  • slide 1 of 5

    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.

  • slide 2 of 5

    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.

  • slide 3 of 5

    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.

  • slide 4 of 5

    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.

  • slide 5 of 5

    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.

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