SQL Subqueries презентация

Subqueries Module 6

Agenda What is subquery? The IN operator Subquery in the FROM clause Subquery as field requests Correlated subquery Conditions EXISTS, NOT EXISTS The UNION operator

What is subquery? 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.

The IN operator 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 <list of fields> FROM <table name> WHERE <field name> IN (<list of values>)

Subquery in the FROM clause 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 as field requests Demonstration

Correlated subquery 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);

Conditions EXISTS, NOT EXISTS 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 );

The UNION operator The UNION operator is used to combine the result-set of two or more SELECT statements. SQL UNION Syntax SELECT <LIST OF FIELDS> FROM <TABLE 1> UNION [ALL] SELECT <LIST OF FIELDS> FROM <TABLE 2>

Thank you!