limited time offer

MySQL Joins and Nested Queries Interview Questions

Showing 1 - 9 of 9 results

What is SELF JOIN in MySQL ?

A self JOIN is a regular join, but the table is joined with itself.

Syntax of MySQL SELF JOIN

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

T1 and T2 are different table aliases for the same table.

Example of MYSQL SELF JOIN

Employee Table

+------------+-----------+----------+--------------+
| EmployeeID | FirstName | LastName | SupervisorID |
+------------+-----------+----------+--------------+
|          1 | Tarun     | Goswami  | 2            |
|          2 | Ram       | Sharma   | NULL         |
|          3 | Rohit     | Verma    | 1            |
|          4 | Ronit     | Roy      | 2            |
|          5 | Bhanu     | Bhardwaj | 1            |
|          6 | Sunil     | Sharma   | 1            |
+------------+-----------+----------+--------------+

MYSQL SELF JOIN Query

Suppose you want to find supervisor of all employee.

use Company;
SELECT E1.EmployeeID, 
CONCAT(E1.FirstName, ' ', E1.LastName) As Name, 
E1.SupervisorID, 
CONCAT(E2.FirstName, ' ', E2.LastName) as SupervisoName
FROM Employee E1
LEFT OUTER JOIN Employee E2 on E1.SupervisorID = E2.EmployeeID

Output

+------------+----------------+--------------+---------------+
| EmployeeID | Name           | SupervisorID | SupervisoName |
+------------+----------------+--------------+---------------+
|          1 | Tarun Goswami  | 2            | Ram Sharma    |
|          2 | Ram Sharma     | NULL         | NULL          |
|          3 | Rohit Verma    | 1            | Tarun Goswami |
|          4 | Ronit Roy      | 2            | Ram Sharma    |
|          5 | Bhanu Bhardwaj | 1            | Tarun Goswami |
|          6 | Sunil Sharma   | 1            | Tarun Goswami |
+------------+----------------+--------------+---------------+

 

What is CROSS JOIN in MySQL ?

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product.
If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

Data of Cross Join can be used for testing purpose or several permutation and combination operations can be performed using Cross Join.

Syntax of MySQL Cross Join

SELECT * 
FROM table1 
CROSS JOIN table2;

Example of MySQL Cross Join

Product Table

+-----------+-----------------+--------------+
| ProductID | ProductName     | ProductPrice |
+-----------+-----------------+--------------+
|         3 | Farm House      |       200.00 |
|         4 | Peppy Panner    |       300.00 |
|         5 | Veggie Paradise |       250.00 |
+-----------+-----------------+--------------+

Sides Table

+---------+----------------------+------------+
| SidesID | SidesName            | SidesPrice |
+---------+----------------------+------------+
|       1 | Garlic Breadsticks   | 90.5       |
|       2 | Stuffed Garlic Bread | 110        |
|       3 | Cheese Dip           | 25.5       |
+---------+----------------------+------------+

MYSQL Cross Join Query 

use Shopping;
SELECT P.ProductName, S.SidesName,(P.ProductPrice+S.SidesPrice) AS TotalCost 
FROM Products P
cross join Sides S;

Output

+-----------------+----------------------+-----------+
| ProductName     | SidesName            | TotalCost |
+-----------------+----------------------+-----------+
| Farm House      | Garlic Breadsticks   |     290.5 |
| Peppy Panner    | Garlic Breadsticks   |     390.5 |
| Veggie Paradise | Garlic Breadsticks   |     340.5 |
| Farm House      | Stuffed Garlic Bread |       310 |
| Peppy Panner    | Stuffed Garlic Bread |       410 |
| Veggie Paradise | Stuffed Garlic Bread |       360 |
| Farm House      | Cheese Dip           |     225.5 |
| Peppy Panner    | Cheese Dip           |     325.5 |
| Veggie Paradise | Cheese Dip           |     275.5 |
+-----------------+----------------------+-----------+

 

 

What is FULL JOIN (Or FULL OUTER JOIN) in MySQL ?

MySQL does not support FULL OUTER JOIN.

Some database systems do not support the full outer join functionality directly, but they can emulate it through the use of an inner join and UNION ALL selects of the "single table rows" from left and right tables respectively. 

Fundamentally, a FULL JOIN is a combination of the effect produced by both a LEFT JOIN and a RIGHT JOIN. Thus we can say that its result set is equivalent to performing a UNION of the results of left and right outer queries.

Example of FULL OUTER JOIN

Customer Table

+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address    | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
|          1 | Tarun        | 9999075499 | Madan Puri |    124 |     122001 |        91 |
|          2 | Ram          | 9650423377 | A-487      |     11 |     110085 |        91 |
|          3 | Sham         | 1111111111 | A-485      |     11 |     110085 |        91 |
|          4 | Mohan        | 1234567890 | 454        |    124 |     122002 |        91 |
+------------+--------------+------------+------------+--------+------------+-----------+

Order Table

+---------+------------+---------------------+-----------+
| OrderID | CustomerID | OrderDate           | ShipperID |
+---------+------------+---------------------+-----------+
|       1 |          1 | 2019-10-02 21:23:12 |        11 |
|       2 |          3 | 2019-10-02 21:23:27 |        12 |
|       3 |          5 | 2019-10-02 21:39:21 |        12 |
+---------+------------+---------------------+-----------+

FULL OUTER JOIN Query

SELECT C.CustomerName, O.OrderID 
FROM Customer C 
INNER JOIN Orders O 
ON C.CustomerID = O.CustomerID 

UNION ALL

SELECT C.CustomerName, NULL
FROM Customer C 
WHERE NOT EXISTS (
    SELECT * FROM Orders O
             WHERE C.CustomerID = O.CustomerID)

UNION ALL

SELECT NULL, O.OrderID
FROM Orders O
WHERE NOT EXISTS (
    SELECT * FROM Customer C
             WHERE C.CustomerID = O.CustomerID);

Output

+--------------+---------+
| CustomerName | OrderID |
+--------------+---------+
| Tarun        |       1 |
| Sham         |       2 |
| Ram          |    NULL |
| Mohan        |    NULL |
| NULL         |       3 |
+--------------+---------+

 

What is RIGHT JOIN (Or RIGHT OUTER JOIN) in MySQL ?

It returns all rows from the right table and the corresponding matching rows from the left table. It is an exact opposite of the LEFT JOIN. Thus the result will contain all the records from the right table, even if the JOIN condition doesn’t find any matching records in the left table.

RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

Example of RIGHT JOIN

Customer Table

+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address    | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
|          1 | Tarun        | 9999075499 | Madan Puri |    124 |     122001 |        91 |
|          2 | Ram          | 9650423377 | A-487      |     11 |     110085 |        91 |
|          3 | Sham         | 1111111111 | A-485      |     11 |     110085 |        91 |
|          4 | Mohan        | 1234567890 | 454        |    124 |     122002 |        91 |
+------------+--------------+------------+------------+--------+------------+-----------+

Order Table

+---------+------------+---------------------+-----------+
| OrderID | CustomerID | OrderDate           | ShipperID |
+---------+------------+---------------------+-----------+
|       1 |          1 | 2019-10-02 21:23:12 |        11 |
|       2 |          3 | 2019-10-02 21:23:27 |        12 |
|       3 |          5 | 2019-10-02 21:39:21 |        12 |
+---------+------------+---------------------+-----------+

RIGHT JOIN Query

use Shopping;
SELECT C.CustomerName, O.OrderID
FROM Customer C
RIGHT JOIN Orders O
ON C.CustomerID = O.CustomerID
ORDER BY C.CustomerName;

Output

+--------------+---------+
| CustomerName | OrderID |
+--------------+---------+
| NULL         |       3 |
| Sham         |       2 |
| Tarun        |       1 |
+--------------+---------+

 

What is LEFT JOIN (Or LEFT OUTER JOIN) in MySQL ?

Returns all rows from the left table, and the matching rows from the right table. Thus the result will contain all records from the left table, even if the JOIN condition doesn’t find any matching records in the right table.

LEFT JOIN Syntax

SELECT colunm_name 
FROM table1 LEFT JOIN table2 
ON table1.colunm_name = table2.colunm_name;

Example of LEFT JOIN

Customer Table

+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address    | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
|          1 | Tarun        | 9999075499 | Madan Puri |    124 |     122001 |        91 |
|          2 | Ram          | 9650423377 | A-487      |     11 |     110085 |        91 |
|          3 | Sham         | 1111111111 | A-485      |     11 |     110085 |        91 |
|          4 | Mohan        | 1234567890 | 454        |    124 |     122002 |        91 |
+------------+--------------+------------+------------+--------+------------+-----------+

Order Table

+---------+------------+---------------------+-----------+
| OrderID | CustomerID | OrderDate           | ShipperID |
+---------+------------+---------------------+-----------+
|       1 |          1 | 2019-10-02 21:23:12 |        11 |
|       2 |          3 | 2019-10-02 21:23:27 |        12 |
|       3 |          5 | 2019-10-02 21:39:21 |        12 |
+---------+------------+---------------------+-----------+

LEFT JOIN Query

use Shopping;
SELECT C.CustomerName, O.OrderID
FROM Customer C
LEFT JOIN Orders O
ON C.CustomerID = O.CustomerID
ORDER BY C.CustomerName;

Output

+--------------+---------+
| CustomerName | OrderID |
+--------------+---------+
| Mohan        |    NULL |
| Ram          |    NULL |
| Sham         |       2 |
| Tarun        |       1 |
+--------------+---------+

What is INNER JOIN ( Also Called As “Simple Join”) in MySQL ?

It returns all the rows for which there is at least one match in BOTH the tables. If join type is not specifically mentioned then “INNER JOIN” works as the default join.

INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

Example of INNER JOIN

Customer Table

+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address    | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
|          1 | Tarun        | 9999075499 | Madan Puri |    124 |     122001 |        91 |
|          2 | Ram          | 9650423377 | A-487      |     11 |     110085 |        91 |
|          3 | Sham         | 1111111111 | A-485      |     11 |     110085 |        91 |
|          4 | Mohan        | 1234567890 | 454        |    124 |     122002 |        91 |
+------------+--------------+------------+------------+--------+------------+-----------+

Order Table

+---------+------------+---------------------+-----------+
| OrderID | CustomerID | OrderDate           | ShipperID |
+---------+------------+---------------------+-----------+
|       1 |          1 | 2019-10-02 21:23:12 |        11 |
|       2 |          3 | 2019-10-02 21:23:27 |        12 |
|       3 |          5 | 2019-10-02 21:39:21 |        12 |
+---------+------------+---------------------+-----------+

INNER JOIN Query

use Shopping;
SELECT C.CustomerName, O.OrderID
FROM Customer C
INNER JOIN Orders O
ON C.CustomerID = O.CustomerID
ORDER BY C.CustomerName;

Output

+--------------+---------+
| CustomerName | OrderID |
+--------------+---------+
| Sham         |       2 |
| Tarun        |       1 |
+--------------+---------+

 

How many types of join in MySQL ?

There are different types of join in the MySQL: 

  • Inner Join
  • Outer Join - Left Outer Join, Right Outer Join, Full Outer Join
  • Self Join
  • Cross Join

Note: Left Outer Join is same as Left Join and Right Outer join is same as Right Join.

What are joins in MySQL ?

MySQL JOINS are used to retrieve data from multiple tables. A MySQL JOIN is performed whenever two or more tables are joined in a SQL statement. The tables are mutually related using primary and foreign keys.

There are different types of MySQL joins:

  • MySQL INNER JOIN (simple join): It is a default join. It returns records when the values match in the joining tables.
  • MySQL LEFT OUTER JOIN (LEFT JOIN): It returns all the records from the left table based on the matched records from the right table.
  • MySQL RIGHT OUTER JOIN ( RIGHT JOIN): It returns all the records from the right table based on the matched records from the left table.
  • MYSQL CROSS JOIN
  • FULL OUTER JOIN: It returns all the records that match from the left or right table.

What Is The Purpose Of A Sub Query in SQL ?

A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

Example of SubQuery

Student Table

+-----------+-------+
| StudentId | Name  |
+-----------+-------+
|         1 | Tarun |
|         2 | Ram   |
|         3 | Sham  |
|         4 | Mohan |
+-----------+-------+

Marks Table

+-----------+------------+
| StudentId | TotalMarks |
+-----------+------------+
|         1 |        500 |
|         2 |        400 |
|         3 |        300 |
|         4 |        700 |
+-----------+------------+

Query

SELECT s.StudentId, s.Name, m.TotalMarks 
FROM sys.student s, sys.marks m 
WHERE s.StudentId = m.StudentId 
AND m.TotalMarks > (SELECT TotalMarks FROM sys.marks WHERE StudentID = 2);

Result

+-----------+-------+------------+
| StudentId | Name  | TotalMarks |
+-----------+-------+------------+
|         1 | Tarun |        500 |
|         4 | Mohan |        700 |
+-----------+-------+------------+

 

Subscribe to MySQL Joins and Nested Queries Interview Questions