Concepts Flashcards
(33 cards)
What does a JOIN operation do in Databricks SQL?
A JOIN operation in Databricks SQL combines rows from a left table with a right table based on specified join criteria.
What is the default join type in Databricks SQL?
The default join type in Databricks SQL is INNER JOIN, which returns only rows with matching values in both tables.
What is the difference between INNER JOIN and LEFT OUTER JOIN?
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.
How does RIGHT OUTER JOIN differ from LEFT OUTER JOIN?
RIGHT OUTER JOIN returns all rows from the right table and matched rows from the left table, inserting NULLs where there is no match.
What is FULL OUTER JOIN in Databricks SQL?
FULL OUTER JOIN returns all rows from both tables, inserting NULLs in columns where there is no match from the other table.
What does LEFT SEMI JOIN return?
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.
What is the function of LEFT ANTI JOIN?
LEFT ANTI JOIN returns rows from the left table that do not have any match in the right table.
What result does a CROSS JOIN produce?
A CROSS JOIN returns the Cartesian product of two tables, combining each row of the first table with all rows from the second table.
What is the purpose of NATURAL JOIN in Databricks SQL?
NATURAL JOIN implicitly matches columns with the same names in both tables, combining rows with equal values in those columns.
What happens if you omit the join criteria in a JOIN operation?
If the join criteria is omitted, any JOIN type will behave like a CROSS JOIN, creating a Cartesian product.
How does the ON clause work in JOINs?
The ON clause defines a boolean condition that specifies how rows from the two tables should be matched.
What does the USING clause do in a JOIN?
USING clause matches rows by comparing equality across specified columns that must exist in both tables.
What effect does USING or NATURAL JOIN have on SELECT *?
Using USING or NATURAL JOIN removes duplicate columns from the result when SELECT * is used, showing matched columns only once.
What is a table alias in the context of JOINs?
A table alias is a temporary name assigned to a table in a JOIN, optionally with column identifiers for clarity or brevity.
How does SELECT * behave differently with USING compared to ON?
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.
How can USING (a, b) be rewritten using ON?
USING (a, b) is equivalent to ON first.a = second.a AND first.b = second.b, with SELECT * excluding repeated a and b columns.
What does a LATERAL JOIN do in Databricks SQL?
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.
What is the difference between LATERAL INNER JOIN and LATERAL LEFT JOIN?
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.
What practical example illustrates an INNER JOIN between employee and department?
An INNER JOIN returns only employees whose deptno exists in department. For example, Paul (deptno 3) joins with Engineering.
What practical example illustrates a LEFT JOIN between employee and department?
A LEFT JOIN returns all employees, with department info for matches and NULLs otherwise. Chloe, Evan, and Amy have NULL deptnames.
What result does a FULL JOIN produce in a real-world example?
It returns all employees and departments, matching where possible and inserting NULLs for unmatched entries.
How does a SEMI JOIN behave in an employee-department example?
It returns only employees with matching department keys, excluding any department columns.
How does an ANTI JOIN behave in an employee-department example?
It returns only employees without a corresponding department in the department table.
What does a CROSS JOIN look like with employee and department?
It creates a Cartesian product where each employee appears once for every department, greatly increasing row count.
