SQL Joins

Question 1

What does a JOIN operation do in Databricks SQL?

Answer 1

A JOIN operation in Databricks SQL combines rows from a left table with a right table based on specified join criteria.

Question 2

What is the default join type in Databricks SQL?

Answer 2

The default join type in Databricks SQL is INNER JOIN, which returns only rows with matching values in both tables.

Question 3

What is the difference between INNER JOIN and LEFT OUTER JOIN?

Answer 3

INNER JOIN returns only matching rows from both tables, while LEFT OUTER JOIN returns all rows from the left table and matched rows from the right, filling in NULLs for unmatched rows.

Question 4

How does RIGHT OUTER JOIN differ from LEFT OUTER JOIN?

Answer 4

RIGHT OUTER JOIN returns all rows from the right table and matched rows from the left table, inserting NULLs where there is no match.

Question 5

What is FULL OUTER JOIN in Databricks SQL?

Answer 5

FULL OUTER JOIN returns all rows from both tables, inserting NULLs in columns where there is no match from the other table.

Question 6

What does LEFT SEMI JOIN return?

Answer 6

LEFT SEMI JOIN returns rows from the left table that have at least one match in the right table, without including columns from the right table.

Question 7

What is the function of LEFT ANTI JOIN?

Answer 7

LEFT ANTI JOIN returns rows from the left table that do not have any match in the right table.

Question 8

What result does a CROSS JOIN produce?

Answer 8

A CROSS JOIN returns the Cartesian product of two tables, combining each row of the first table with all rows from the second table.

Question 9

What is the purpose of NATURAL JOIN in Databricks SQL?

Answer 9

NATURAL JOIN implicitly matches columns with the same names in both tables, combining rows with equal values in those columns.

Question 10

What happens if you omit the join criteria in a JOIN operation?

Answer 10

If the join criteria is omitted, any JOIN type will behave like a CROSS JOIN, creating a Cartesian product.

Question 11

How does the ON clause work in JOINs?

Answer 11

The ON clause defines a boolean condition that specifies how rows from the two tables should be matched.

Question 12

What does the USING clause do in a JOIN?

Answer 12

USING clause matches rows by comparing equality across specified columns that must exist in both tables.

Question 13

What effect does USING or NATURAL JOIN have on SELECT *?

Answer 13

Using USING or NATURAL JOIN removes duplicate columns from the result when SELECT * is used, showing matched columns only once.

Question 14

What is a table alias in the context of JOINs?

Answer 14

A table alias is a temporary name assigned to a table in a JOIN, optionally with column identifiers for clarity or brevity.

Question 15

What does a JOIN operation do in Databricks SQL?

Answer 15

A JOIN operation in Databricks SQL combines rows from a left table with a right table based on specified join criteria.

Question 16

What is the default join type in Databricks SQL?

Answer 16

The default join type in Databricks SQL is INNER JOIN, which returns only rows with matching values in both tables.

Question 17

What is the difference between INNER JOIN and LEFT OUTER JOIN?

Answer 17

INNER JOIN returns only matching rows from both tables, while LEFT OUTER JOIN returns all rows from the left table and matched rows from the right, filling in NULLs for unmatched rows.

Question 18

How does RIGHT OUTER JOIN differ from LEFT OUTER JOIN?

Answer 18

RIGHT OUTER JOIN returns all rows from the right table and matched rows from the left table, inserting NULLs where there is no match.

Question 19

What is FULL OUTER JOIN in Databricks SQL?

Answer 19

FULL OUTER JOIN returns all rows from both tables, inserting NULLs in columns where there is no match from the other table.

Question 20

What does LEFT SEMI JOIN return?

Answer 20

LEFT SEMI JOIN returns rows from the left table that have at least one match in the right table, without including columns from the right table.

Question 21

What is the function of LEFT ANTI JOIN?

Answer 21

LEFT ANTI JOIN returns rows from the left table that do not have any match in the right table.

Question 22

What result does a CROSS JOIN produce?

Answer 22

A CROSS JOIN returns the Cartesian product of two tables, combining each row of the first table with all rows from the second table.

Question 23

What is the purpose of NATURAL JOIN in Databricks SQL?

Answer 23

NATURAL JOIN implicitly matches columns with the same names in both tables, combining rows with equal values in those columns.

Question 24

What happens if you omit the join criteria in a JOIN operation?

Answer 24

If the join criteria is omitted, any JOIN type will behave like a CROSS JOIN, creating a Cartesian product.

Question 25

How does the ON clause work in JOINs?

Answer 25

The ON clause defines a boolean condition that specifies how rows from the two tables should be matched.

Question 26

What does the USING clause do in a JOIN?

Answer 26

USING clause matches rows by comparing equality across specified columns that must exist in both tables.

Question 27

What effect does USING or NATURAL JOIN have on SELECT \*?

Answer 27

Using USING or NATURAL JOIN removes duplicate columns from the result when SELECT \* is used, showing matched columns only once.

Question 28

What is a table alias in the context of JOINs?

Answer 28

A table alias is a temporary name assigned to a table in a JOIN, optionally with column identifiers for clarity or brevity.

Question 29

How does SELECT \* behave differently with USING compared to ON?

Answer 29

With USING, SELECT \* displays only one instance of each matched column, followed by all other columns from the left and then the right table, excluding the duplicated columns.

Question 30

How can USING (a, b) be rewritten using ON?

Answer 30

USING (a, b) is equivalent to ON first.a = second.a AND first.b = second.b, with SELECT \* excluding repeated a and b columns.

Question 31

What does a LATERAL JOIN do in Databricks SQL?

Answer 31

A LATERAL JOIN allows a subquery on the right side of the JOIN to reference columns from the left table, effectively making the subquery dependent on each row of the left table.

Question 32

What is the difference between LATERAL INNER JOIN and LATERAL LEFT JOIN?

Answer 32

LATERAL INNER JOIN returns only rows with matches in the subquery, while LATERAL LEFT JOIN returns all left rows and NULLs for non-matching subquery results.

Question 33

What practical example illustrates an INNER JOIN between employee and department?

Answer 33

An INNER JOIN returns only employees whose deptno exists in department. For example, Paul (deptno 3) joins with Engineering.

Question 34

What practical example illustrates a LEFT JOIN between employee and department?

Answer 34

A LEFT JOIN returns all employees, with department info for matches and NULLs otherwise. Chloe, Evan, and Amy have NULL deptnames.

Question 35

What result does a FULL JOIN produce in a real-world example?

Answer 35

It returns all employees and departments, matching where possible and inserting NULLs for unmatched entries.

Question 36

How does a SEMI JOIN behave in an employee-department example?

Answer 36

It returns only employees with matching department keys, excluding any department columns.

Question 37

How does an ANTI JOIN behave in an employee-department example?

Answer 37

It returns only employees without a corresponding department in the department table.

Question 38

What does a CROSS JOIN look like with employee and department?

Answer 38

It creates a Cartesian product where each employee appears once for every department, greatly increasing row count.