SQL Joins Interview Questions

1. What is an SQL Join?

An SQL join is used to combine rows from two or more tables based on a related column between them.

2. Explain INNER JOIN in SQL.

INNER JOIN returns records that have matching values in both tables. Rows from both tables that do not satisfy the condition are excluded.

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

3. What are joins in SQL?

A join clause is a SQL command used to combine records from multiple tables or retrieve data from these tables based on the existence of a common field (column) between them. A join condition and SELECT statement can be used to join the tables. Using the SQL JOIN clause, records can be fetched from two or more tables in a database and combined. In general, they are used when users need to retrieve data from tables that contain many-to-many or one-to-many relationships between them.

Example: Let's take a look at two tables. Here’s the Employee table.

Here's the Employment table. 

Let us now join these two tables together using a SELECT statement, as shown below.

SELECT Emp_ID, Emp_Name, Emp_No, Emp_Profile, Emp_Country FROM Employee, Employment WHERE  Employee.Emp_ID = Employment.Emp_ID;

Output:

4. What is the importance of SQL joins in database management?

SQL joins are important in database management for the following reasons:

• A method of stitching a database back together to make it easier to read and use.
• Additionally, they maintain a normalized database. Data normalization helps us keep data redundancy low so that when we delete or update a record, we will have fewer data anomalies in our application.
• Joins have the advantage of being faster, and as a result, are more efficient.
• It is almost always faster to retrieve the data using a join query rather than one that uses a subquery.
• By utilizing joins, it is possible to reduce the workload on the database. For example, instead of multiple queries, you can use one join query. So, you can better utilize the database's ability to search, filter, sort, etc.

5. Explain merge join in SQL.

Merge join produces a single output stream resulting from the joining of two sorted datasets using an INNER, FULL, or LEFT join. It is the most effective of all the operators for joining data. Specifically, merge join requires that both inputs be sorted as well as matching meta-data in the joined columns. Users can't join columns of different data types together. Users are not permitted to combine a column with a numeric data type with a column with a character data type.

6. State the difference between inner join and left join.

• Inner Join: This join generates datasets that contain matching records in both tables (left and right). By using an inner join, only the rows that match between each of the tables are returned; all non-matching rows are removed.

Example: Let's take a look at two tables. Here’s the Tb1_Employee table.

Here's the Tb2_Employment table.

Let’s perform INNER JOIN on these two tables using a SELECT statement, as shown below: 

SELECT Emp_Name, Emp_No, Emp_Profile, Emp_Country, Emp_Join_Date FROM Tb1_Employee INNER JOIN Tb2_Employment ON Tb1_Employee.Emp_ID=Tb2_Employment.Emp_ID;

Output: 

• Left Join: It returns datasets that have matching records in both tables (left and right) plus non-matching rows from the left table. By using a left join, all the records in the left table plus the matching records in the right table are returned.

Example: Let’s now perform LEFT JOIN on these two tables using a SELECT statement, as shown below: 

SELECT Tb1_Employee.Emp_Name, Tb1_Employee.Emp_No, Tb2_Employment.Emp_Profile, Tb2_Employment.Emp_Country FROM Tb1_Employee LEFT JOIN Tb2_Employment ON Tb1_Employee.Emp_ID=Tb2_Employment.Emp_ID;

Output:

7. What is a hash join in SQL?

Just like any other join, the hash join requires two inputs, which are the probe input (inner table) and the build input (outer table). A hash join involves the use of a hash table to identify rows matching between two tables. The hash join is the option when no other join is preferred (possibly due to the absence of sorting or indexing etc). Hash joins are best when joining large data sets that are unsorted and non-indexed.

8. Can you explain nested join in SQL?

A JOIN is one of the mechanisms that we use to combine the data of more than one table in a relational database, and a Nested Join is one of the simplest methods involving the physical joining of two tables. In essence, a Nested Join uses one joining table as an outer input table while the other one serves as an inner input table. With a Nested Loop Join, one row from the outer table is retrieved and then the row is searched for in the inner table; this process is repeated until all the output rows from the outer table have been searched for in the inner table. Nested Loop Join may further be sub-categorized into Indexed Nested, Naive Nested and Temporary Index Nested Loop Join. 

9. Write an SQL query to join three tables.

At times, you might need to retrieve data from three or more tables at once. A multi-table join requires consecutive JOIN operations: the first and second tables are joined to form a virtual table and then the third table is joined to this virtual table. Let's take a look at three tables. 

Here’s the Employee table.

Here's the Employment table. 

Here’s the EmpDetail.

SELECT Emp_Name, Emp_No, Emp_Profile, Emp_Country, EmpJoinDate, FROM Employee e INNER JOIN Employment m ONe.Emp_ID = m.EMP_ID INNER JOIN EmpDetail d on d.Emp_Email = m.Emp_Email;

Output:

10. How can you join a table to itself?

Another type of join in SQL is a SELF JOIN, which connects a table to itself. In order to perform a self-join, it is necessary to have at least one column (say X) that serves as the primary key as well as one column (say Y) that contains values that can be matched with those in X. The value of Column Y may be null in some rows, and Column X need not have the exact same value as Column Y for every row.

Example: Consider the table Employees.

For instance, we might wish to display results that only include employees with their managers. By using table aliases and a self-join, this can be accomplished easily. 

SELECT e.Emp_ID, e.Emp_Name, m.FullName as ManagerNameFROM Employees e JOIN Employees m ON e.ManagerId = m.Emp_ID

Output:

11. How should data be structured to support Join Operations in a one-to-many relationship?

Among the most common types of database relationships are the ones involving one-to-many relationships.  Consider the example of the Customer and Mobile table. 

Customer Table:

Mobile Table:

A customer can own many mobile phones, but a mobile belongs to one customer only. Therefore, we've defined the Foreign Key column (Customer_ID) in the Mobile table allowing us to perform SELECT queries with JOIN clauses fairly easily.

12. How should data be structured to support Join Operations in a many-to-many relationship?

The many-to-many relationship is a bit more complex than the one-to-many relationship.

Example: Consider a Student table and a Class table, both of which have many-to-many relationships: any student can be enrolled in many classes, and any class can have many students. It means an individual student may have many classes, and there may be many students in each class. 

Student Table

Class Table

In this case, we cannot add the primary key of one table to that of another, or to both of them, as this only stores a single relationship, while what we really need is multiple relationships. Thus, we use a concept called a bridging table or joining table. The joining tables are those that are placed between two other tables in a many-to-many relationship and are intended to hold a record for each combination of the two other tables. It may seem like quite a chunk of work, but the process is simple and provides a much better data structure. In this case, we will create a new table called ClassEnroll maintaining two columns, one for each of the primary keys of the other table. Those columns store separate records for every class and student combination.

ClassEnroll Table

13. Explain natural join.

Natural Joins are a type of join that combines tables based on columns that share the same name and have the same datatype. Ideally, there should be a common attribute (column) among two tables in order to perform a natural join.

Syntax:

SELECT * FROM TableName1 NATURAL JOIN TableName2;

Example: Consider two tables Employee and Employment.

Employee

Employment

Now, consider the following query.

SELECT * FROM Employee NATURAL JOIN Employment;

Output:

14. What is a Non-Equi Join?

A Non-Equi join entails pulling data from multiple tables by using an INNER join. This type of join matches the columns of two tables based on an inequality using operators such as <, <=, >, >=, !=, BETWEEN, etc.

Syntax:

SELECT *  FROM TableName1, TableName2  WHERE TableName1.columnName [> | < |  >= | <= | != | BETWEEN ] TableName2.columnName;

15. Is it required that the Join condition be based on equality?

No, joins can have non-equi conditions as well. Join clauses can be used with common comparison operators, such as <, <=, >, >=, !=, BETWEEN. For example, listing records, listing unique pairs, and identifying duplicate records are just a few situations where non-equi joins can prove to be useful.

16. Explain CTE (Common Table Expression) SQL.

Generally, Common Table Expressions (CTEs) are temporary named result sets that you can use to refer to within an UPDATE, INSERT, SELECT, or DELETE statement. The CTEs can be defined by adding a WITH before an UPDATE, INSERT, DELETE, SELECT, OR MERGE statement. Several CTEs may be used within the WITH clause, separated by commas.

17. Distinguish between nested subquery, correlated subquery, and join operation.

• Join Operation: A join operation is a binary operation that combines the data or rows from two or more tables based on a field they have in common. There are different types of joins, such as INNER JOIN, FULL JOIN, LEFT JOIN, RIGHT JOIN, etc.
• Subquery: A query can be enclosed within another query, so the outer query is called the main query, and the inner query is called a subquery.
  • Nested Query: Nested queries execute the inner query first, and only once. An outer query is then executed based on the results of the inner query. The inner query is therefore used to execute the outer query.
  • Correlated Query: The outer query is executed first, and for every row of the outer query, the inner query is executed. Thus, values from the outer query are used in the inner query.

18. Code Snippet: LEFT JOIN in SQL

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

19. Online Resources:

• W3Schools SQL Joins
• PostgreSQL Joins Tutorial