SQL Joins Interview Questions and Answers

Q1. What is SQL JOIN’s?

Ans: An SQL join clause 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.

Q2. What are the different types of SQL JOINs ?

Ans: The list of different types of SQL JOINs as follows:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

Q3. What is left outer join?

Ans: SQL LEFT JOIN Keyword. The LEFT JOIN keyword returns all records from the lefttable (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

Q4. What is a full outer join?

Ans: SQL FULL OUTER JOIN Keyword. The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!

Q5. Can you join a table to itself in SQL?

Ans: Using Self-Joins. A table can be joined to itself in a self-join. Use a self-join when you want to create a result set that joins records in a table with other records in the same table. To list a table two times in the same query, you must provide a table alias for at least one of instance of the table name

Q6. What is Right Join in SQL?

Ans: Right join is used to retrieve rows which are common between the tables and all rows of Right hand side table. It returns all the rows from the right hand side table even though there are no matches in the left hand side table

Q7. What is Left Join in SQL?

Ans: Left join is used to retrieve rows which are common between the tables and all rows of Left hand side table. It returns all the rows from Left hand side table even though there are no matches in the Right hand side table.

Q8. What is Full Join in SQL?

Ans: Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.

Q9. What is Difference between Inner Join and Outer Join?

Ans: Both inner join and outer join are the joining technique where matching records from the participating tables are displayed. That is when two tables are joined, based on the common column in both the tables, records are pulled.
In inner join method, when two tables are joined, only those records from both the tables are retrieved for which there exact match is based on the common column. This common column is based on the primary key of one table and is foreign key in other table. When there is exact match for these two column values records are retrieved.

 Q10. What is a Self Join?

Ans: A self-join is simply a normal SQL join that joins one table to itself. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.

Q11. Is Self Join Inner Join or Outer Join?

Ans: A self-join can be an inner join or an outer join or even a cross join. A table is joined to itself based upon a column that have duplicate data in different rows.

Q12. What is the difference between LEFT JOIN and RIGHT JOIN?

Ans:LEFT JOIN and RIGHT JOIN actually both do very similar things: they display the results of a JOIN query including all records on a given table. The only difference is that LEFT JOIN displays all records on the left table of the query, and RIGHT JOIN displays all records on the right table!