SQL Interview Questions

In SQL, joins are used to combine rows from two or more tables based on a related column between them. The main types of joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. Each type of join specifies how SQL should return data from the tables involved.

1. INNER JOIN

INNER JOIN returns only the rows that have matching values in both tables.

• Syntax:

  SELECT columns
  FROM table1
  INNER JOIN table2
  ON table1.common_column = table2.common_column;

• Example:

  SELECT employees.name, departments.department_name
  FROM employees
  INNER JOIN departments
  ON employees.department_id = departments.department_id;

• Result:

  • Only rows where department_id exists in both employees and departments will be returned.

2. LEFT JOIN (or LEFT OUTER JOIN)

LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.

• Syntax:

  SELECT columns
  FROM table1
  LEFT JOIN table2
  ON table1.common_column = table2.common_column;

• Example:

  SELECT employees.name, departments.department_name
  FROM employees
  LEFT JOIN departments
  ON employees.department_id = departments.department_id;

• Result:

  • All rows from employees will be returned.
  • If an employee does not have a matching department_id in departments, NULL will be returned for department_name.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the side of the left table.

• Syntax:

  SELECT columns
  FROM table1
  RIGHT JOIN table2
  ON table1.common_column = table2.common_column;

• Example:

  SELECT employees.name, departments.department_name
  FROM employees
  RIGHT JOIN departments
  ON employees.department_id = departments.department_id;

• Result:

  • All rows from departments will be returned.
  • If a department does not have a matching department_id in employees, NULL will be returned for name.

4. FULL OUTER JOIN

FULL OUTER JOIN returns all rows when there is a match in either the left or right table. If there is no match, the result is NULL from the side where there is no match.

• Syntax:

  SELECT columns
  FROM table1
  FULL OUTER JOIN table2
  ON table1.common_column = table2.common_column;

• Example:

  SELECT employees.name, departments.department_name
  FROM employees
  FULL OUTER JOIN departments
  ON employees.department_id = departments.department_id;

• Result:

  • All rows from employees and all rows from departments will be returned.
  • If there is no match, NULL will be returned for the columns of the table that does not have a match.

Summary

• INNER JOIN: Returns only the rows with matching values in both tables.
• LEFT JOIN: Returns all rows from the left table, and matched rows from the right table (NULL if no match).
• RIGHT JOIN: Returns all rows from the right table, and matched rows from the left table (NULL if no match).
• FULL OUTER JOIN: Returns all rows when there is a match in either table (NULL if no match).

Understanding these joins is crucial for effective data querying and manipulation in SQL, enabling you to retrieve precisely the data you need based on the relationships between tables.