SQL Subqueries

Сентябрь 7, 2022

Содержание

2. What is subquery? The IN operator Subquery in the FROM clause Subquery as field requests Correlated
3. A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement,
4. Subqueries can be used with the following SQL statements along with the comparison operators like =,
5. Subqueries are legal in a SELECT statement's FROM clause. The syntax for the query: SELECT ...
6. Demonstration Subquery as field requests
7. Correlated subquery is a subquery that uses values from the outer query. The subquery is evaluated
8. The SQL EXISTS condition is used in combination with a subquery and is considered to be
9. The UNION operator is used to combine the result-set of two or more SELECT statements. SQL
11. Скачать презентацию

Слайд 2

What is subquery?
The IN operator
Subquery in the FROM clause
Subquery as field

requests
Correlated subquery
Conditions EXISTS, NOT EXISTS
The UNION operator

Agenda

Слайд 3

A subquery is a query that is nested inside a SELECT,

INSERT, UPDATE, or DELETE statement, or inside another subquery.
A subquery can be used anywhere an expression is allowed.
There are a few rules that subqueries must follow:
Subqueries must be enclosed within parentheses.
A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
An ORDER BY cannot be used in a subquery.
Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.
The BETWEEN operator cannot be used with a subquery; however, the BETWEEN operator can be used within the subquery.

What is subquery?

Слайд 4

Subqueries can be used with the following SQL statements along with

the comparison operators like
=, <, >, >=, <= etc.
Usually, a subquery should return only one record, but sometimes it can also return multiple records when used with operators IN, NOT IN in the where clause.
The query syntax:
SELECT
FROM
WHERE IN ()

The IN operator

Слайд 5

Subqueries are legal in a SELECT statement's FROM clause.
The syntax

for the query:
SELECT ...
FROM (subquery) [AS] name ...
The [AS] name clause is mandatory, because each table in a FROM clause must have a name.
Any columns in the subquery select list must have unique names.

Subquery in the FROM clause

Слайд 6

Demonstration
Subquery as field requests

Слайд 7

Correlated subquery is a subquery that uses values from the outer query.

The subquery is evaluated once for each row processed by the outer query.
For example,
SELECT ID, LASTNAME
FROM EMPLOYEE AS EMP
WHERE RATE > ( SELECT AVG(RATE)
FROM EMPLOYEE
WHERE
ID_DEPARTMENT= EMP.ID_DEPARTMENT);

Correlated subquery

Слайд 8

The SQL EXISTS condition is used in combination with a subquery

and is considered to be met, if the subquery returns at least one row.
It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
The syntax for the EXISTS condition is:
WHERE EXISTS ( subquery );

Conditions EXISTS, NOT EXISTS

Слайд 9