SQL Interview Questions

SQL joins are used to combine rows from two or more tables based on a related column between them. Joins are essential for querying data that is spread across multiple tables, allowing you to retrieve a more comprehensive dataset. The primary types of SQL joins are:

1. INNER JOIN

Description: 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.id;

2. LEFT (OUTER) JOIN

Description: Returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for columns from 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.id;

3. RIGHT (OUTER) JOIN

Description: Returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for columns from 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.id;

4. FULL (OUTER) JOIN

Description: Returns all rows when there is a match in either left or right table. Rows without a match in either table will contain NULL for the columns of the table that lacks a match.

Syntax:

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

Example:

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

5. CROSS JOIN

Description: Returns the Cartesian product of both tables, meaning it combines all rows of the first table with all rows of the second table.

Syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

Example:

SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

6. SELF JOIN

Description: Joins a table with itself to compare rows within the same table.

Syntax:

SELECT a.columns, b.columns
FROM table a
INNER JOIN table b
ON a.common_column = b.common_column;

Example:

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
INNER JOIN employees e2
ON e1.manager_id = e2.id;

7. NATURAL JOIN

Description: Automatically joins tables based on columns with the same name and compatible data types. It is essentially an inner join but without explicitly specifying the columns.

Syntax:

SELECT columns
FROM table1
NATURAL JOIN table2;

Example:

SELECT name, department_name
FROM employees
NATURAL JOIN departments;

Each type of join serves a specific purpose and is used based on the relationship between the tables and the desired result set. Understanding the different types of joins and their uses is crucial for effective database querying and data retrieval.