Explore

Data Management Foundations - D426

Unit 3: Complex Queries

Makiel [Muh-Keel]

Last edited 271 days ago by Makiel [Muh-Keel].

3.1 Special Operators and Clauses

IN operator

The IN operator is used in a WHERE clause to determine if a value matches one of several values.

The SELECT statement in the figure below uses the IN operator to select only rows where the Language column has a Dutch, Kongo, or Albanian value.

⁠

BETWEEN Operator

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.

MySQL compiles a BETWEEN expression and the equivalent comparison operator expression to the same executable code. So the two expressions run in the same time.

⁠

LIKE operator

The LIKE operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.

% matches any number of characters. Ex: LIKE 'L%t' matches "Lt", "Lot", "Lift", and "Lol cat".

_ matches exactly one character. Ex: LIKE 'L_t' matches "Lot" and "Lit" but not "Lt" and "Loot".

The LIKE operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the BINARY keyword.

Ex: LIKE BINARY 'L%t' matches 'Left' but not 'left'.

To search for the wildcard characters % or _, a backslash (\) must precede % or _. Ex: LIKE 'a\%' matches "a%".

⁠

DISTINCT clause

The DISTINCT clause is used with a SELECT statement to return only unique or 'distinct' values.

Ex: The first SELECT statement in the figure below results in two 'Spanish' rows, but the second SELECT statement returns only unique languages, resulting in only one 'Spanish' row.

⁠

ORDER By clause

The ORDER BY clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order.

The DESC keyword with the ORDER BY clause orders rows in descending order.

A SELECT statement selects rows from a table with no guarantee the data will come back in a certain order.

⁠

3.2 Simple Functions

Function

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.

⁠

String functions

String functions manipulate string values. SQL string functions are similar to string functions in programming languages like Java and Python.

⁠

Date and time functions

Date and time functions operate on DATE, TIME, and DATETIME data types.

⁠

3.3 Aggregate Functions

An aggregate function processes values from a set of rows and returns a summary value. Common aggregate functions are:

COUNT() counts the number of rows in the set.

MIN() finds the minimum value in the set.

MAX() finds the maximum value in the set.

SUM() sums all the values in the set.

AVG() computes the arithmetic mean of all the values in the set.

⁠

GROUP BY clause

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.

GROUP BY clauses appears in two places:

between the WHERE clause

between the ORDER BY clause.

⁠

HAVING clause

The HAVING clause is used with the GROUP BY clause to filter group results. The optional HAVING clause follows the GROUP BY clause and precedes the optional ORDER BY clause.

⁠

Aggregate functions and NULL values

Aggregate functions ignore NULL values.

Ex: SUM(Salary) adds all non-NULL salaries and ignores rows containing a NULL salary.

⁠

3.4 Join Queries

A join is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result.

The tables are combined by comparing columns from the left and right tables, usually with the = operator. The columns must have comparable data types.

The first table that appears in the FROM clause of a join statement is called the left table. The second is called the right table.

In relational databases, reports are commonly generated from data in multiple tables. Multi-table reports are written with join statements.

Usually, a join compares a foreign key of one table to the primary key of another. However, a join can compare any columns with comparable data types.

⁠

You are using the join queries in this way:

⁠

SELECT DepartmentName, EmployeeName - is stating the names of the column names you want to select.

This SELECT statement is the actual join query.

FROM Department, Employee - is stating the names of the tables you are choosing these column names from.

WHERE Manager = ID; - is stating you are using the “Manager = ID” to link the two columns together.

To link DepartmentName and EmployeeName using the mutual ‘manager= ID’ values.

These values are the primary key and foreign key values.

Prefixes and aliases

When duplicate column names appear in a query, the names must be distinguished with a prefix. The prefix is the table name followed by a period.

Occasionally, join tables contain columns with the same name.

AS (Aliases)

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.

Use of a prefix makes column names more complex.

Inner and full joins

A join clause determines how a join query handles unmatched rows. Two common join clauses are:

INNER JOIN selects only matching left and right table rows.

INNER JOIN only shows rows where both tables have a match.

FULL JOIN selects all left and right table rows, regardless of match.

FULL JOIN shows everything from both tables, even if there are gaps.

In a FULL JOIN result table, unmatched left table rows appear with NULL values in right table columns, and vice versa.

The join clause appears between a FROM clause and an ON clause:

The FROM clause specifies the left table.

The INNER JOIN or FULL JOIN clause specifies the right table.

The ON clause specifies the join columns.

Left and right joins

In some cases, the database user wants to see unmatched rows from either the left or right table, but not both. To enable these cases, relational databases support left and right joins:

LEFT JOIN selects all left table rows, but only matching right table rows.

RIGHT JOIN selects all right table rows, but only matching left table rows.

An outer join is any join that selects unmatched rows, including left, right, and full joins.

⁠

In this example:

When joining the Department and Employee tables based on Manager = ID, an unmatched row would be any row in one table that doesn’t find a matching value in the other table.

For instance:

In the LEFT JOIN result, the "Technical support" department is an unmatched row because it has no manager listed in the Employee table (the Manager field is NULL), resulting in a NULL value for Supervisor.

In the RIGHT JOIN result, "Sam Snead" in the Employee table is an unmatched row because no department lists him as a manager, resulting in a NULL value for the Group (department name).

Key Points:

LEFT JOIN: Shows all rows from the left table (Department), with NULL for columns in the right table (Employee) where there’s no match.

RIGHT JOIN: Shows all rows from the right table (Employee), with NULL for columns in the left table (Department) where there’s no match.

Unmatched rows help to identify data in one table that doesn’t relate directly to data in another, which can be useful for spotting incomplete relationships.

Alternative join queries

Inner and outer joins can be written without a JOIN clause. However, the JOIN clause clarifies join behavior and simplifies queries.

Thus, use of the JOIN clause is good practice.

Union Keyword combines two results into one table.

⁠

3.5 Equijoins, self-joins, and cross-joins

Equijoins

An equijoin compares columns of two tables with the = operator. Most joins are equijoins.

Non-Equijoins

A non-equijoin compares columns with an operator other than =, such as < and >.

Comparison operators such as < and > can be used in the ON clause following any JOIN clause.

⁠

Self-Join

A self-join joins a table to itself. A self-join can compare any columns of a table, as long as the columns have comparable data types.

A self-join compares two different columns of the same table.

If a foreign key and the referenced primary key are in the same table, a self-join commonly compares those key columns.

In a self-join, aliases are necessary to distinguish left and right tables.

⁠

Cross-Joins

A cross-join combines two tables without comparing columns. A cross-join uses a CROSS JOIN clause without an ON clause.

A cross-join selects all possible combinations of left and right table rows. Since all row combinations are shown, no comparison is specified.

As a result, all possible combinations of rows from both tables appear in the result.

⁠

3.6 Subqueries

A subquery, sometimes called a nested query or inner query, is a query within another SQL query.

The subquery is typically used in a SELECT statement's WHERE clause to return data to the outer query and restrict the selected results.

The subquery is placed inside parentheses (), meaning it proceeds the outer-query and is done first.

Whatever value the query grabs, is then placed in the outer query.

⁠

Correlated Subqueries

A subquery is correlated when the subquery's WHERE clause references a column from the outer query.

In a correlated subquery, the rows selected depend on what row is currently being examined by the outer query.

If a column name in the correlated subquery is identical to a column name in the outer query, the TableName.ColumnName differentiates the columns.

Ex: City.CountryCode refers to the City table's CountryCode column .

3.7 Complex Query Example

3.8 View Tables

A view table is a table name associated with a SELECT statement, called the view query.

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.

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.