Correlated Subqueries Flashcards
What is a correlated subquery?
It’s a subquery that uses information from the outer query
T/F: A correlated subquery is run on every row of the outer query
True
When would you want to use a correlated subquery?
You’d want to use one when the subquery depends on the value of each row in the outer query.
T/F: You have to alias the outer query’s table so it can be referenced in the inner subquery
True, e.g.
SELECT first_name, salary, department FROM employees outerr
WHERE salary > (SELECT ROUND(AVG(salary), 2)
FROM employees
WHERE department = outerr.department)
Use implicit join syntax to join employees and regions on region_id
SELECT * FROM employees, regions WHERE employees.region_id = regions.region_id
What type of JOIN is an implicit JOIN?
It performs an INNER JOIN
Why are implicit joins bad practice?
Because they are less explicit in their syntax, which can make it difficult to read/track what is happening in a more complex query
How would you rewrite this to use explicit syntax?
SELECT first_name, region FROM employees, regions WHERE employees.region_id = regions.region_id
SELECT first_name, region FROM employees INNER JOIN regions ON employees.region_id = regions.region_id
What is the function of an INNER JOIN?
It joins two tables on a single column, and returns the rows where the values in that column are equal in a single table
What is the order of operations in this query?:
SELECT first_name, email, division, country FROM employees e INNER JOIN departments d ON e.department = d.department INNER JOIN regions r ON e.region_id = r.region_id WHERE email IS NOT NULL
- FROM clause is executed from top to bottom, i.e. the first INNER JOIN is performed, then the next INNER JOIN
- WHERE clause is executed, filtering on email column
- SELECT statement returns specified columns
Why does this fail?
SELECT department FROM employees e INNER JOIN departments d ON employees.department = departments.department
Because department exists in both tables and is ambiguously called in the SELECT statement. You need to add a table prefix to specify which department column you want to return, e.g.:
SELECT e.department FROM employees e INNER JOIN departments d ON employees.department = departments.department
What is the function of a LEFT JOIN?
It returns ALL the values from the left table, and only the matching values from the right table. Any non-matching rows will have NULL in the right table columns.
What is the function of a RIGHT JOIN?
It returns ALL the values from the right table, and only the matching values from the left table. Any non-matching rows will have NULL in the left table columns.
What is LEFT/RIGHT JOIN also known as?
LEFT/RIGHT OUTER JOIN
What does FULL OUTER JOIN return?
It returns all match and non-match rows in a single table
In words using JOIN syntax, how would you find all of the values that DON’T exist in the right joining column but exist in the left joining column?
Do a LEFT JOIN on the column, then only return rows where the right column is NULL
Why does this error?
SELECT DISTINCT department, region_id FROM employees UNION ALL SELECT department, division FROM departments
Because the type of region_id and division are different, preventing them from being concatenated
What is happening in this query?
SELECT DISTINCT department FROM employees UNION SELECT department FROM departments
It is concatenating the top and bottom query values together and removing duplicates
What is the difference between UNION and UNION ALL?
Both will concatenate one query result after the other, but UNION will remove duplicate values while UNION ALL will not
T/F: you can only UNION one column at a time
False. Multiple columns can be UNION’d as long as the paired columns are the same type
What does this query return?
SELECT DISTINCT department FROM employees EXCEPT SELECT department FROM departments
It returns the values from the employees table which DO NOT exist in the departments table
What is the equivalent of EXCEPT in Oracle?
MINUS
What type of JOIN does this return?
SELECT * FROM departments, employees
It returns a CROSS JOIN, or Cartesian Product, of both of the tables. This means that every row from departments will be joined with every row of employees, resulting in and row length of len(departments) * len(employees)
What’s a more explicit way of writing this query?
SELECT * FROM departments, employees
SELECT * FROM departments CROSS JOIN employees
