2. Complex Queries Flashcards
IN
The IN operator is used in a WHERE clause to determine if a value matches one of several values.
BETWEEN
The BETWEEN operator provides an alternative way to determine if a value is between two other values. The operator is written value BETWEEN minValue AND maxValue and is equivalent to value >= minValue AND value <= maxValue.
LIKE
The LIKE operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.
BINARY
The LIKE operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the BINARY keyword.
DISTINCT
The DISTINCT clause is used with a SELECT statement to return only unique or ‘distinct’ values.
ORDER BY
The ORDER BY clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order.
DESC
The DESC keyword with the ORDER BY clause orders rows in descending order.
function / argument
A function operates on an expression enclosed in parentheses, called an argument, and returns a value. Usually, the argument is a simple expression, such as a column name or fixed value. Some functions have several arguments, separated by commas, and a few have no arguments at all.
aggregate function
An aggregate function processes values from a set of rows and returns a summary value.
COUNT()
COUNT() counts the number of rows in the set.
MIN()
MIN() finds the minimum value in the set.
MAX()
MAX() finds the maximum value in the set.
SUM()
SUM() sums all the values in the set.
AVG()
AVG() computes the arithmetic mean of all the values in the set.
GROUP BY
The GROUP BY clause consists of the GROUP BY keyword and one or more columns. Each simple or composite value of the column(s) becomes a group. The query computes the aggregate function separately, and returns one row, for each group.
HAVING
The HAVING clause is used with the GROUP BY clause to filter group results.
join / left table / right table
A join is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result.
AS
To simplify queries or result tables, a column name can be replaced with an alias. The alias follows the column name, separated by an optional AS keyword.
join clause
A join clause determines how a join query handles unmatched rows. Two common join clauses are:
INNER JOIN
INNER JOIN selects only matching left and right table rows.
FULL JOIN
FULL JOIN selects all left and right table rows, regardless of match.
ON
The ON clause specifies the join columns.
LEFT JOIN
LEFT JOIN selects all left table rows, but only matching right table rows.
RIGHT JOIN
RIGHT JOIN selects all right table rows, but only matching left table rows.
outer join
An outer join is any join that selects unmatched rows, including left, right, and full joins.
UNION
The UNION keyword combines the two results into one table.
equijoin
An equijoin compares columns of two tables with the = operator.
non-equijoin
A non-equijoin compares columns with an operator other than =, such as < and >.
self-join
A self-join joins a table to itself.
cross-join
A cross-join combines two tables without comparing columns.
CROSS JOIN
A cross-join uses a CROSS JOIN clause without an ON clause.
subquery / nested query / inner query
A subquery, sometimes called a nested query or inner query, is a query within another SQL query.
correlated
A subquery is correlated when the subquery’s WHERE clause references a column from the outer query.
alias
An alias is a temporary name assigned to a column or table.
AS
The AS keyword follows a column or table name to create an alias.
EXISTS
Correlated subqueries commonly use the EXISTS operator, which returns TRUE if a subquery selects at least one row and FALSE if no rows are selected.
NOT EXISTS
The NOT EXISTS operator returns TRUE if a subquery selects no rows and FALSE if at least one row is selected.
flattening
Replacing a subquery with an equivalent join
Most subqueries that follow IN or EXISTS, or return a single value, can be flattened. Most subqueries that follow NOT EXISTS or contain a GROUP BY clause cannot be flattened.
The following steps are a first pass at flattening a query:
Retain the outer query SELECT, FROM, GROUP BY, HAVING, and ORDER BY clauses.
Add INNER JOIN clauses for each subquery table.
Move comparisons between subquery and outer query columns to ON clauses.
Add a WHERE clause with the remaining expressions in the subquery and outer query WHERE clauses.
If necessary, remove duplicate rows with SELECT DISTINCT.
After this first pass, test the flattened query and adjust to achieve the correct result. Verify that the original and flattened queries are equivalent against a variety of data.
view table / view query
A view table is a table name associated with a SELECT statement, called the view query.
CREATE VIEW
The CREATE VIEW statement creates a view table and specifies the view name, query, and, optionally, column names. If column names are not specified, column names are the same as in the view query result table.
base table
A table specified in the view query’s FROM clause is called a base table.
materialized view
A materialized view is a view for which data is stored at all times.
WITH CHECK OPTION
When WITH CHECK OPTION is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause.
