3.4 Join queries

Question 1

What is a join in relational databases?

Which operator does it use?

Answer 1

1. A join is a SQL SELECT statement that combines data from two tables into a single result by comparing columns.
2. Typically uses 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.

1. The left table mentioned first
2. Then 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

1. Prefixes: using the table name followed by a period)
2. Aliases (using the AS keyword)

either of these methods will help the database system understand which column you’re referring to when there are duplicates

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

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

Question 6

What does a LEFT JOIN do?

Answer 6

1. A LEFT JOIN returns all rows from the left table and the matching rows from the right table.
2. 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 Name and Department Name on their Code fields?

Answer 9

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

Use INNER JOIN for only the matching rows.

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 by using a combination of WHERE clauses and the UNION operator to ensure unmatched rows are included.

LEFT JOIN can be represented without the JOIN keyword

Question 11

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

Answer 11

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

Also known as specifying the condition

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

How can you use more than two tables in a JOIN query?

When someone says “join query,” they could be referring to any of the 6 specific types of joins

Answer 13

1. You can join multiple tables by chaining multiple JOIN clauses.
2. Then specify 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

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

Question 20

How doJOIN statements affect the performance of a query?

Answer 20

• JOIN statements are essential for combining data from multiple tables in relational databases.
  • While they can impact performance, especially with large datasets, proper indexing mitigates this.
• This avoids redundancy and enables complex queries.

Question 21

What happens to unmatched rows in a FULL OUTER JOIN?

Answer 21

1. In a FULL OUTER JOIN, all rows from both tables are included in the result set.
2. When a row has a match in the other table, the corresponding columns from both tables are combined in the output.
3. However, when a row doesn’t have a match in the other table, the columns from the other table for that row will be filled with NULL values.

Question 22

What is the distinction between a join and a subquery?

Answer 22

1. A join combines data from multiple tables in a single query.
2. A subquery is a query nested inside another SQL query, often used to produce a 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? ```SELECT Faculty.FacultyName, Department.DepartmentName FROM Faculty RIGHT JOIN Department ON Faculty.code = Department.code ORDER BY Department Name;```

Answer 31

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

Question 32

What's the difference between UNION and INNER join?

Answer 32

* Use the UNION approach for *a complete view*, including unmatched rows. * Use INNER JOIN for only the matching rows.

Question 33

What is the key relationship used in an INNER JOIN?

Answer 33

You typically join using a foreign key in one table that references the primary key in another.

Question 34

What is the key relationship used in a LEFT JOIN? ## Footnote Just like with a left and right join, you can think of a full outer join as encompassing both tables. You retrieve all records from both tables, filling in `NULL` values where there are no matches.

Answer 34

You use a *foreign key in the left table* to reference the primary key in the right table.

Question 35

What is the key relationship used in a RIGHT JOIN? ## Footnote Just like with a left and right join, you can think of a full outer join as encompassing both tables. You retrieve all records from both tables, filling in `NULL` values where there are no matches.

Answer 35

You use the *foreign key in the right table* which references the primary key in the left table.

Question 36

What is the key relationship used in a FULL OUTER JOIN?

Answer 36

Still uses primary and foreign keys; it shows all entries from both tables.