SQL Joins & Subqueries

Joins and subqueries are two of the most powerful features of SQL. They allow you to combine data from two or more tables in a single query.

SQL Joins

A SQL join combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each. For example, you can use a JOIN clause to combine rows from two or more tables based on a related column between them.

Types of Joins

1. INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables. It returns rows when there is at least one match in both tables. For example, if you have two tables employees and departments, you can use an INNER JOIN to get the employees along with their department names.

• Employees Table:

• Departments Table:

The following SQL query will return the employees along with their department names:

SELECT
  employees.emp_id,
  employees.emp_name,
  departments.dept_name
FROM
  employees
  INNER JOIN departments ON employees.emp_dept = departments.dept_id;

2. LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match. For example, if you have two tables students and courses, you can use a LEFT JOIN to get all students along with the courses they are enrolled in.

• Students Table:

• Courses Table:

The following SQL query will return all students along with the courses they are enrolled in:

SELECT
  students.student_id,
  students.student_name,
  courses.course_name
FROM
  students
  LEFT JOIN courses ON students.student_id = courses.student_id;

Note: In the above example, David is not enrolled in any course, so the course_name is NULL.

3. RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side when there is no match. For example, if you have two tables orders and customers, you can use a RIGHT JOIN to get all orders along with the customer details.

• Orders Table:

• Customers Table:

The following SQL query will return all orders along with the customer details:

SELECT
  orders.order_id,
  orders.order_date,
  customers.customer_name
FROM
  orders
  RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

Note: In the above example, David and Eve are customers who have not placed any orders, so the order_id and order_date are NULL.

4. FULL JOIN

The FULL JOIN keyword returns all records when there is a match in either the left (table1) or right (table2) table records. It returns NULL on the side where there is no match. For example, if you have two tables students and courses, you can use a FULL JOIN to get all students along with the courses they are enrolled in.

• Students Table:

• Courses Table:

The following SQL query will return all students along with the courses they are enrolled in:

SELECT
  students.student_id,
  students.student_name,
  courses.course_name
FROM
  students FULL
  JOIN courses ON students.student_id = courses.student_id;

Note: In the above example, David and Eve are students who are not enrolled in any course, so the course_name is NULL.

SQL Subqueries

A subquery is a query nested within another query. It is used to return data that will be used in the main query as a condition. Subqueries can be used with SELECT, INSERT, UPDATE, and DELETE statements. They are also known as inner queries or nested queries.

1. Subquery in SELECT Statement

You can use a subquery in the SELECT statement to retrieve data from another table based on a condition. For example, if you want to find the total number of employees in each department, you can use a subquery in the SELECT statement.

SELECT
  emp_dept,
  (
    SELECT
      COUNT(*)
    FROM
      employees
    WHERE
      emp_dept = departments.dept_id
  ) AS total_employees
FROM
  departments;

2. Subquery in WHERE Clause

You can use a subquery in the WHERE clause to filter the result set based on a condition. For example, if you want to find the employees who earn more than the average salary in their department, you can use a subquery in the WHERE clause.

SELECT
  emp_id,
  emp_name,
  emp_salary
FROM
  employees
WHERE
  emp_salary > (
    SELECT
      AVG(emp_salary)
    FROM
      employees
    WHERE
      emp_dept = employees.emp_dept
  );

3. Subquery in FROM Clause

You can use a subquery in the FROM clause to create a temporary table that can be used in the main query. For example, if you want to find the total number of employees in each department, you can use a subquery in the FROM clause.

SELECT
  emp_dept,
  total_employees
FROM
  (
    SELECT
      emp_dept,
      COUNT(*) AS total_employees
    FROM
      employees
    GROUP BY
      emp_dept
  ) AS temp_table;

4. Subquery in INSERT Statement

You can use a subquery in the INSERT statement to insert data into a table based on the result of another query. For example, if you want to insert the employees who earn more than the average salary in their department into a new table, you can use a subquery in the INSERT statement.

INSERT INTO
  high_salary_employees (emp_id, emp_name, emp_salary)
SELECT
  emp_id,
  emp_name,
  emp_salary
FROM
  employees
WHERE
  emp_salary > (
    SELECT
      AVG(emp_salary)
    FROM
      employees
    WHERE
      emp_dept = employees.emp_dept
  );

5. Subquery in UPDATE Statement

You can use a subquery in the UPDATE statement to update data in a table based on the result of another query. For example, if you want to update the salary of the employees who earn less than the average salary in their department, you can use a subquery in the UPDATE statement.

UPDATE
  employees
SET
  emp_salary = emp_salary + 5000
WHERE
  emp_salary < (
    SELECT
      AVG(emp_salary)
    FROM
      employees
    WHERE
      emp_dept = employees.emp_dept
  );

6. Subquery in DELETE Statement

You can use a subquery in the DELETE statement to delete data from a table based on the result of another query. For example, if you want to delete the employees who earn less than the average salary in their department, you can use a subquery in the DELETE statement.

DELETE FROM
  employees
WHERE
  emp_salary < (
    SELECT
      AVG(emp_salary)
    FROM
      employees
    WHERE
      emp_dept = employees.emp_dept
  );