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.