14 - SQL JOINS And UNION CLAUSE

We have seen how to retrieve data from multiple tables in previous chapter. There should be some relation between two tables and matching those we can get data from both the tables.

There are two types of CLAUSE to get data from multiple tables: JOINS and UPDATE.

SQL JOIN:

The JOIN clause is used to retrieve data by combining two or more tables in a database. There are different types of JOINS:

  1. INNER JOIN:

INNER JOIN also known as SIMPLE or EQUI JOIN. This is the  most commonly used JOIN. This type of JOIN returns all rows from the multiple tables when the JOIN condition is met.     

Syntax:

SELECT columns

FROM table1

INNER JOIN table2

ON table1.column = table2.column;

Consider the tables

  1. Customer table
+-------------------+------------------+------------------+
|cust_id         | cust_name     | cust_area     |
+-------------------+------------------+------------------+
|       1        | James         | OH            |
|       2        | Ivy           | OH            |
|       3        | Linda         | PH            |
|       4        | Max           | PH            |
|       5        | Mike          | CL            |
+-------------------+------------------+------------------+

2.  Movie_TRAN table.

+-------------------+---------------------------+------------------+------------------+
|movie_id     | moviename              | cust_id     | tran_date      |
+-------------------+---------------------------+------------------+------------------+
|      111     | LIFE IS BEAUTIFUL     |       2     | 2014-11-01     |
|      123     | GRAVITY               |       2     | 2014-12-01     |
|      134     | HELLBOY               |       4     | 2013-04-22     |
+-------------------+---------------------------+------------------+------------------+

3. Movie_details

+-------------------+----------------------------+---------------------------+------------------+
|movie_id      | moviename             | director               | year_release |
+-------------------+----------------------------+---------------------------+------------------+
|      111     | Life is beautiful     | Roberto Beningni       |         1997 |
|      123     | gravity               | Alfonso Cuaron         |         2013 |
|      134     | Hellboy               | Guillermo del Toro     |         2004 |
|      144     | Transformers          | Michael Bay            |         2007 |
+-------------------+----------------------------+---------------------------+------------------+

The example of getting details from CUSTOMER and MOVIE_TRAN table which was written as below:

 

mysql> SELECT CUSTOMER.CUST_ID,CUST_NAME,MOVIENAME

    -> FROM CUSTOMER, MOVIE_TRAN

    -> WHERE CUSTOMER.CUST_ID = MOVIE_TRAN.CUST_ID;

This can be written as below:

 

mysql> SELECT CUSTOMER.CUST_ID, CUST_NAME, MOVIENAME

    -> FROM CUSTOMER

    -> INNER JOIN MOVIE_TRAN

The output will be:

+------------+-----------------------+----------------------------+
| CUST_ID | CUST_NAME     | MOVIENAME         |
+------------+-----------------------+----------------------------+
|       2 | Ivy           | LIFE IS BEAUTIFUL |
|       2 | Ivy           |GRAVITY            |
|       4 | Max           | HELLBOY           |
+------------+-----------------------+----------------------------+
3 rows in set (0.00 sec)

 2. LEFT JOIN:

This type of JOIN returns all rows from Left table and matched rows from the right table.

            

Syntax:

SELECT columns

FROM table1

LEFT [OUTER] JOIN table2

ON table1.column = table2.column;

Example:           

Consider the example:

mysql>  SELECT CUSTOMER.CUST_ID, CUST_NAME, MOVIENAME

    -> FROM CUSTOMER

    -> LEFT JOIN MOVIE_TRAN

    -> ON CUSTOMER.CUST_ID = MOVIE_TRAN.CUST_ID;

The output would be:

+-------------+----------------------+---------------------------+
|CUST_ID  | CUST_NAME  | MOVIENAME             |
+-------------+----------------------+---------------------------+
|       2 | Ivy        | LIFE IS BEAUTIFUL     |
|       2 | Ivy        | GRAVITY               |
|       4 | Max        | HELLBOY               |
|       1 | James      | NULL                  |
|       3 | Linda      | NULL                  |
|       5 | Mike       | NULL                  |
+-------------+----------------------+---------------------------+
6 rows in set (0.05 sec)

Here is you notice, for CUST_ID 1, 3 and 5 Movie name is NULL this is because these customers have not rented any movie. The Left Join will get all the records from left table here the CUSTOMER table and if there is no match in right table then NULL value will be displayed.

3. RIGHT JOIN:

RIGHT JOIN also known as RIGHT OUTER JOIN. In this type of JOIN, the right table is matched with left table and all rows of right table are returned, and only matched rows from left table.

         

Syntax:

 

SELECT columns

FROM table1

RIGHT [OUTER] JOIN table2

ON table1.column = table2.column; 

Example:

Get the transaction date and the rows of MOVIE_DETAILS table.

mysql> SELECT MD.MOVIE_ID,MD.MOVIENAME, TRAN_DATE
    -> FROM MOVIE_TRAN MT
    -> RIGHT JOIN MOVIE_DETAILS MD
    -> ON MD.MOVIE_ID = MT.MOVIE_ID;
+-----------------+---------------------------+--------------------+
|MOVIE_ID      | MOVIENAME             | TRAN_DATE  |
+-----------------+---------------------------+--------------------+
|      111     | Life is beautiful     | 2014-11-01 |
|      123     | gravity               | 2014-12-01 |
|      134     | Hellboy               | 2013-04-22 |
|      144     | Transformers          | NULL       |
+-----------------+---------------------------+--------------------+
4 rows in set (0.03 sec)

Here the right table MOVIE_TRAN is matched with left table MOVIE_DETAILS. All rows of MOVIE_DETAILS are displayed and the transaction date of only the movie_ids matching with Movie_tran are returned. If movie_id is not matched then NULL is  returned.

4. FULL JOIN:

FULL JOIN or FULL OUTER JOIN returns all the rows of left and right table. It combines the result of LEFT and RIGHT joins.

      

Syntax:

SELECT columns

FROM table1

FULL [OUTER] JOIN table2

ON table1.column = table2.column;

Important point to note: mySQL does not support FULL OUTER JOIN.  It is possible in other implementations.

Example:

 

     SELECT CS.CUST_NAME, MT.TRAN_DATE

    -> FROM CUSTOMER CS

    -> FULL OUTER JOIN MOVIE_TRAN MT

    -> ON CS.CUST_ID = MT.CUST_ID;

UNION:

As name suggests UNION operator is the union of two or more SELECT statements and give result set without any duplicate data/rows. This allows you to get information from one or more tables having same number of columns and corresponding columns having identical data types and lengths.

Basic Syntax 

 

SELECT COL1,COL2,COL3,…COLN

FROM TABLES

WHERE CONDITION

UNION

SELECT COL1,COL2,COL3,…COLN

FROM TABLES

WHERE CONDITION

Example:

Consider there are tables giving sales details of different regions as below:

SOUTH_REG 

Salesperson_name

Sales

Smith

  1.  

Jane

  1.  

Alex

  1.  

NORTH_REG

Salesperson_name

Sales

James

  1.  

Jesse

  1.  

Linda

  1.  

Jane

  1.  

 

 

SELECT * FROM SOUTH_REG

UNION

SELECT * FROM NORTH_REG;

Output would be:

Salesperson_name

Sales

Smith

  1.  

Jane

  1.  

Alex

  1.  

James

  1.  

Jesse

  1.  

Linda

  1.  

You can use ORDER BY clause to display records in order.

 

SELECT * FROM SOUTH_REG

UNION

SELECT * FROM NORTH_REG

ORDER BY SALESPERSON_NAME;

     

Salesperson_name

Sales

Alex

  1.  

James

  1.  

Jane

  1.  

Jesse

  1.  

Linda

  1.  

Smith

  1.  

UNION ALL:           

UNION ALL is similar to UNION operator, just that UNION ALL will give all the records including the duplicates generated from SELECT Statements. Same rules apply to UNION ALL as in UNION.

Syntax:

 

SELECT col1,col2, col3,….colN

FROM table

WHERE condition

UNION ALL

SELECT col1, col2, col3,…colN

FROM table

WHERE condition;

Example :

Consider the same example of Sales of NORTH and SOUTH REGION

 

SELECT * FROM SOUTH_REG

UNION ALL

SELECT * FROM NORTH_REG;

Output would be as below. You might have noticed, there is a duplicate row for salesperson “Jane”.

Salesperson_name

Sales

Smith

  1.  

Jane

  1.  

Alex

  1.  

James

  1.  

Jesse

  1.  

Linda

  1.  

Jane

  1.  

You can order this result as well by using ORDER BY clause.

 

SELECT * FROM SOUTH_REG

UNION ALL

SELECT * FROM NORTH_REG

ORDER BY SALESPERSON_NAME;

 Output would be:

Salesperson_name

Sales

Alex

  1.  

James

  1.  

Jane

  1.  

Jane

  1.  

Jesse

  1.  

Linda

  1.  

Smith

  1.  

Like us on Facebook