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:
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
- 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 | |
Jane | |
Alex |
NORTH_REG
Salesperson_name | Sales |
James | |
Jesse | |
Linda | |
Jane |
SELECT * FROM SOUTH_REG UNION SELECT * FROM NORTH_REG; |
Output would be:
Salesperson_name | Sales |
Smith | |
Jane | |
Alex | |
James | |
Jesse | |
Linda |
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 | |
James | |
Jane | |
Jesse | |
Linda | |
Smith |
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 | |
Jane | |
Alex | |
James | |
Jesse | |
Linda | |
Jane |
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 | |
James | |
Jane | |
Jane | |
Jesse | |
Linda | |
Smith |