SQL Joins Interview Questions

SQL Joins Interview Questions for Experienced

11. What are the types of join based on comparing operators?

The are two types of SQL JOINS depending on comparing operators:-

1. SQL Equi Join is a simple SQL join that uses the comparison operator equal sign(=) for the condition. It is of two types SQL Inner join and SQL Outer join.
2. SQL Non-Equi Join is a join that uses a comparison operator other than the equal sign with the condition. It can be >, <, >=, or <=.

12. What are Non-Equi Joins?

The non-equi joins are all those JOIN types that use some other comparison operators. It can be greater than (>), less than (<), less than or equal to (<=), or greater than or equal to (>=).

There are three main uses for this type of join:

1. They are used to find duplicates in tables.
2. They are used to join tables on a range of values.
3. They are used to calculate running totals.

13. Is there any way to join a table to itself?

You can join a table to itself in SQL using a type of join known as a SELF JOIN. It joins a table to itself. A minimum of one column (let's say X) that acts as the primary key and one column (let's say Y) that has values that can be matched with those in X are required to accomplish a self-join. Column X does not always need to have the same value as Column Y for every row, and Column Y's value may be null in certain rows.

14. Explain nested joins in SQL.

A Nested Join uses two tables. One joining table is an outer input table, while the other is an inner input table. A Nested Loop Join involves:

1. Retrieving one row from the outside table.
2. Searching for it in the inner table.
3. Repeating this procedure until all the output rows from the outer table are searched for in the inner table.

Naive Nested, Indexed Nested, and Temporary Index Nested Loop Join are subcategories of Nested Loop Join.

15. What is the difference between Join and Subquery?

1. JOINs are mostly faster than a subquery, and the opposite is rare. 
2. RDBMS has some pre-calculations in JOINs and hence helps save time, unlike the subquery. 
3. A JOIN first checks conditions, puts them into a table and displays them. In contrast, a subquery takes a separate temp table internally and checks the condition.
4. When joins are used, there should be a connection between two or more tables. Also, each table should have a relation with the other. In comparison, subquery means a query inside another query. It does not need to be related. It works on conditions and columns.

16. What pre-calculation is done by RDBMS before executing JOINs and subqueries?

RDBMS calculates an execution plan in JOINs. This helps them to predict what data will be loaded and how much processing time it will require. This helps to save time while joining databases and performing queries on them. While executing subqueries, no pre-calculation is done. RDBMS runs all the queries and loads data for processing. Thus we can say JOINs are better than subqueries.

17. Is it required that the Join condition should always be equal?

No, there are non-equi conditions also present in joins. Join clauses have common comparison operators, like <, <=, >, >=, !=, BETWEEN. For example, listing unique pairs, listing records, and identifying duplicate records are a few situations where non-equi joins are useful.

18. Explain the term CTE (Common Table Expression) SQL.

CTE stands for Common Table Expressions. They are temporarily named result sets that you referred to within a SELECT, INSERT, DELETE, or UPDATE statement. These CTEs are defined by adding a WITH before a SELECT, INSERT, DELETE, UPDATE, OR MERGE statement. Many CTEs are used within the WITH clause and are separated by commas.

19. What do you understand by hash join in SQL?

Hash join is also a type of join in SQL. It requires two inputs: the inner table (probe input) and the outer table (build input). This join involves using a hash table to identify rows matching between two tables. The hash join is preferred when no other join is preferred. It is due to the absence of indexing or sorting etc. Hash joins are best for joining large data sets that are non-indexed and unsorted.

20. Why is a Join clause different from a Union clause?

You can use either JOINS or UNIONS in your queries to combine data from two or more tables.

1. Join: Joins let you horizontally combine the data into new columns. A SQL join clause is used when combining records from various tables or retrieving data from them based on the presence of a shared field (column). The columns of the linking tables may be different when using JOIN. 
2. Union: When more than one SELECT statement's results need to be combined, you can use the term UNION in SQL. You can merge data into new vertical rows using union. All queries must comply with UNION's requirements about the number of columns and their order.

21. Distinguish between types of subquery and join operations.

1. Join: A join operation is a binary operation. It combines the rows or data based on a common field from two or more tables. Different types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, etc.
2. Subquery: It is a query enclosed within another query. In this, the inner query is known as a subquery. While the outer query is known as the main query. There are two types of subqueries:-
   
   1. Nested Query: It first executes the inner query, and only once. Then an outer query is executed depending on the results of the inner query. Therefore the inner query is used to execute the outer query.
   2. Correlated Query: It first executes the outer query. For every outer query row, the inner query is executed. Therefore, values from the outer query are used in the inner query.

22. How can I write SQL syntax to join three tables?

Here is an example of an SQL query to join three tables:

SELECT students.std_id, students.first_name, students.last_name, books.book_id, books.pub_date, book.total_amount
FROM students
JOIN books ON students.std_id = books.std_id
JOIN books_items ON books.book_id = books_items.books_id;

This query will join the students, books, and books_items tables together, using the std_id and book_id columns.

23. How can I formulate a query to retrieve the Customer Name and Product for individuals who have been assigned to more than one project?

The following is an example of a complete SQL query to retrieve the Customer Name and Product for individuals who have been assigned to more than one project:

SELECT Customers.CustomerName, Products.Product, Projects.ProjectName
FROM Customers
JOIN ProjectAssignments ON Customers.CustomerID = ProjectAssignments.CustomerID
JOIN Projects ON ProjectAssignments.CustomerID = Projects.CustomerID
WHERE ProjectAssignments.CustomerID IN (SELECT CustomerID FROM ProjectAssignments GROUP BY CustomerID HAVING COUNT(*) > 1)
ORDER BY Customers.CustomerName;

This will provide you a list of all the customers who have more than one project assigned to them, along with the products they are working on and the projects they are assigned to.

24. How can I write a query to display the name of customers who have not been assigned any products?

The following is an example of a complete SQL query to display the name of customers who have not been assigned any products:

SELECT Customers.CustomerName
FROM Customers
LEFT JOIN ProductAssignments ON Customers.CustomerID = ProductAssignments.CustomerID
WHERE ProductAssignments.CustomerID IS NULL;

This query will return a list of all customers without any assigned products.

25. Difference between Cross join and Natural join.

Natural Join combines two tables based on similar attribute names and data types, and the resulting table consists of the attributes from both tables by keeping only one copy of each common column. Therefore, two tables are combined on the basis of their similar column names, and the table in output consists of only one instance of the common column.

When the user specifies no condition, then the rows are returned on the basis of common columns.

Syntax

SELECT * FROM Table_Name1 NATURAL JOIN Table_Name2;

Cross Join provides a cartesian product of two tables and is used for combining each row from one table to the row of another table. The resulting table consists of the cartesian product, i.e., attributes of both tables (duplicate columns are included).

When the user specifies no condition, then all the possible pairs of rows from the provided tables are returned, even if they are unmatched or matched.

Syntax

SELECT * FROM Table_Name1 CROSS JOIN Table_Name2;

SQL Joins MCQ Questions

1. What type of JOIN returns only the rows that have matching values in both tables?

• a) INNER JOIN
• b) LEFT JOIN
• c) RIGHT JOIN
• d) FULL JOIN

Answer: a) INNER JOIN

2. Which SQL JOIN will return all rows from the left table and the matched rows from the right table?

• a) INNER JOIN
• b) LEFT JOIN
• c) RIGHT JOIN
• d) FULL JOIN

Answer: b) LEFT JOIN

3. If you want to include all records when there is a match in either left or right table, which JOIN should you use?

• a) INNER JOIN
• b) LEFT JOIN
• c) RIGHT JOIN
• d) FULL JOIN

Answer: d) FULL JOIN

4. What is the result of a RIGHT JOIN between two tables?

• a) Only rows that match in both tables
• b) All rows from the left table and the matched rows from the right table
• c) All rows from the right table and the matched rows from the left table
• d) All rows from both tables

Answer: c) All rows from the right table and the matched rows from the left table

5. Which SQL JOIN can result in a Cartesian product if there is no ON condition specified?

• a) INNER JOIN
• b) LEFT JOIN
• c) RIGHT JOIN
• d) CROSS JOIN

Answer: d) CROSS JOIN

6. Which JOIN will return rows from both tables, even if there is no match?

• a) INNER JOIN
• b) LEFT JOIN
• c) RIGHT JOIN
• d) FULL JOIN

Answer: d) FULL JOIN

7. What does a SELF JOIN do?

• a) Joins a table with itself
• b) Joins a table with another table
• c) Joins a table with a subquery
• d) Joins multiple tables at once

Answer: a) Joins a table with itself

8. Which SQL clause is used with JOINs to specify the condition for joining?

• a) SELECT
• b) ON
• c) WHERE
• d) HAVING

Answer: b) ON

9. What type of JOIN should you use to include rows from the left table that do not have matching records in the right table?

• a) INNER JOIN
• b) LEFT JOIN
• c) RIGHT JOIN
• d) FULL JOIN

Answer: b) LEFT JOIN

10. Which JOIN type is most appropriate to combine rows when one table has a one-to-many relationship with another?

• a) INNER JOIN
• b) LEFT JOIN
• c) RIGHT JOIN
• d) CROSS JOIN

Answer: b) LEFT JOIN

Conclusion

This article covers the top 30 SQL Joins interview questions and answers. These questions include basic, intermediate, and advanced topics. They are commonly asked in SQL interviews and will help you prepare for different levels of difficulty.

Recommended Readings:

• SDET Interview Questions
• Powershell Interview Question
• Production Support Interview Questions
• DHCP Interview Questions
• PEGA Interview Questions
• Selenium Interview Questions
• Manual testing interview questions
• Azure Data Engineer Interview Questions
• Spark Interview Questions
•  HTML interview questions