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.
