3.4 Join queries

Question 1

What is a join in relational databases?

Answer 1

A join is a SQL SELECT statement that combines data from two tables into a single result by comparing columns, typically using the ‘=’ operator for columns with comparable data types.

Question 2

When writing a join query, how are the left and right tables defined?

Answer 2

The left and right tables are specified in the FROM clause, with the left table mentioned first and the join clause indicating the right table.

Question 3

In a join where duplicates exist in column names, what tools can be used to resolve this issue?

Answer 3

Prefixes (using the table name followed by a period) and aliases (using the AS keyword) can distinguish columns with the same name.

Question 4

What is the purpose of the INNER JOIN clause?

Answer 4

The INNER JOIN clause selects only those rows that have matching values in both tables being joined.

Question 5

How does a FULL JOIN differ from an INNER JOIN?

Answer 5

A FULL JOIN selects all rows from both tables, returning NULL values where there are no matches, while INNER JOIN only returns rows with matching values.

Question 6

What does a LEFT JOIN do?

Answer 6

A LEFT JOIN returns all rows from the left table and the matching rows from the right table; if there’s no match, NULLs appear in columns from the right table.

Question 7

How can you combine results from LEFT and RIGHT joins in SQL?

Answer 7

You can use the UNION operator to combine the results from a LEFT JOIN and a RIGHT JOIN to include all relevant rows from both tables.

Question 8

What are the consequences of using duplicate column names in your SQL results?

Answer 8

Duplicate column names can lead to ambiguity in the result set; hence, proper prefixes or aliases are necessary to clarify which table the data is coming from.

Question 9

How would you write a query to join the tables Faculty and Department on their Code fields?

Answer 9

SELECT FacultyName, DepartmentName 
FROM Faculty INNER JOIN Department 
ON Faculty.Code = Department.Code;

Question 10

What SQL statement can be used to perform an alternative to INNER JOIN while ensuring unmatched rows are included?

Answer 10

An equivalent LEFT JOIN can be represented without the JOIN keyword by using a combination of WHERE clauses and the UNION operator to ensure unmatched rows are included.

Question 11

What is the significance of the ON clause in a join statement?

Answer 11

The ON clause specifies the columns used to join the tables, indicating how to match rows between the left and right tables.

Question 12

What result does a RIGHT JOIN yield when there’s no match found?

Answer 12

A RIGHT JOIN will include all rows from the right table and NULLs for the columns of the left table where no match is found.

Question 13

Can you use more than two tables in a join query? If so, how?

Answer 13

Yes, you can join multiple tables by chaining multiple JOIN clauses, specifying the conditions for each table’s JOIN using ON.

Question 14

How do you handle situations where you want to join two tables that don’t have related columns?

Answer 14

When there are no related columns, a CROSS JOIN can be used, which produces a Cartesian product of the two tables.

Question 15

In what scenario would you use a self-join?

Answer 15

A self-join is used to join a table with itself to compare rows within the same table, often used for hierarchical or recursive data relationships.

Question 16

What is the result set of an INNER JOIN if there are no matching rows?

Answer 16

If there are no matching rows, the result set of an INNER JOIN will be empty.

Question 17

What does the LEFT OUTER JOIN clause do?

Answer 17

A LEFT OUTER JOIN returns all records from the left table along with matched records from the right table, showing NULL values for unmatched rows from the right.

Question 18

How do you simplify column names in a result set created by a join?

Answer 18

By using aliases with the AS keyword to rename the output columns, making the results easier to read.

Question 19

What is the purpose of the UNION operator in SQL?

Answer 19

The UNION operator is used to combine the result sets of two or more SELECT statements, ensuring that duplicate rows are removed from the output.

Question 20

Can JOIN statements affect the performance of a query? How?

Answer 20

Yes, JOIN statements can affect performance, especially with large datasets, as they may require substantial processing to match rows, especially if indexes are not properly used.

Question 21

What happens to unmatched rows in a FULL OUTER JOIN?

Answer 21

In a FULL OUTER JOIN, unmatched rows from both tables are included, with NULLs filled in for the columns of the table that doesn’t have a matching row.

Question 22

What is the distinction between a join and a subquery?

Answer 22

A join combines data from multiple tables in a single query, while a subquery is a query nested inside another SQL query, often used to produce filter criteria or additional data.

Question 23

Well it’s the result of the following query?

SELECT FacultyName, DepartmentName 
FROM Faculty
FULL JOIN Department 
ON Faculty.Code = Department.Code;

Answer 23

Question 24

What is the result of the following query?

SELECT Department.Name AS Group, Employee.Name AS Supervisor
FROM Department
INNER JOIN Employee
ON Manager = ID;

Answer 24

The INNER JOIN returns only the rows with matching Manager and ID values.

Question 25

What is the result of the following query?

SELECT Department.Name AS Group, Employee.Name AS Supervisor
FROM Department
LEFT JOIN Employee
ON Manager = ID;

Answer 25

The LEFT JOIN returns all rows from the Department table and matching rows from the Employee table. Unmatched rows will return NULL.

Question 26

What is the result of the following query?

SELECT Department.Name AS Group, Employee.Name AS Supervisor
FROM Department
RIGHT JOIN Employee
ON Manager = ID;

Answer 26

The RIGHT JOIN returns all rows from the Employee table and matching rows from the Department table. Unmatched rows will return NULL.

Question 27

What is the result of the following query?

SELECT Department.Name AS Group, Employee.Name AS Supervisor
FROM Department
FULL JOIN Employee
ON Manager = ID;

Answer 27

The FULL JOIN returns all rows from both tables, showing NULLs where there are no matches.

Question 28

What is the result of the following query?

SELECT Department.Name, Employee.Name
FROM Department, Employee
WHERE Manager = ID
UNION
SELECT Department.Name, NULL
FROM Department
WHERE Manager IS NULL;

Answer 28

This query first performs an INNER JOIN and then adds unmatched Department rows using UNION.

Question 29

What is the result of the following query?

SELECT FacultyName, DepartmentName
FROM Faculty
LEFT JOIN Department
ON Faculty.Code = Department.Code;

Answer 29

The LEFT JOIN returns all faculties and the corresponding departments when available.

Question 30

What is the result of the following query?

SELECT *
FROM Faculty
LEFT JOIN Department
ON Faculty.Code = Department.Code
WHERE DepartmentName IS NULL;

Answer 30

This identifies faculty with no matching department.

Question 31

What is the result of the following query?

Answer 31

The RIGHT JOIN returns all departments, sorted by their names, with faculty if available.