1. No category

CSED421
Database Systems Lab
Nested Queries
Index


Union
Subquery





Comparison operators
ANY, ALL
IN, NOT IN
EXISTS, NOT EXISTS
Subquery in FROM clause
Page 2
UNION


The SQL UNION operator combines the result of two or more SELECT
statements.
SQL UNION Syntax


SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Notice


Each SELECT statement within the UNION must have the same number of
columns.
The columns must also have similar data types.



(ex. INT & DOUBLE (O), but INT & CHAR (X))
Also, the columns in each SELECT statement must be in the same order.
The UNION operator selects only distinct values by default.
Page 3
UNION ALL


To allow duplicate values, use the ALL keyword with UNION.
SQL UNION ALL Syntax


Notice

Each SELECT statement within the UNION must have the same number of columns.
The columns must also have similar data types.

(ex. INT & DOUBLE (O), but INT & CHAR (X))
Also, the columns in each SELECT statement must be in the same order.

The UNION ALL operator allow duplicated values.



SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
p.s.

The column names in the result-set =
the column names in the first SELECT statement in the UNION.
Page 4
UNION & UNION ALL Example
SELECT City FROM Customers UNION
SELECT City FROM Suppliers
Order by City
SELECT City FROM Customers UNION ALL
SELECT City FROM Suppliers
Order by City
City
City
Aachen
Aachen
Albuquerque
Albuquerque
Anchorage
Anchorage
Ann Arbor
Ann Arbor
Annecy
Annecy
Barcelona
Barcelona
Barquisimeto
Barquisimeto
Bend
Bend
Bergamo
Bergamo
Berlin
Berlin
Bern
Berlin
…… (94 Records)
…… (120 Records)
Page 5
Subquery

A subquery is a SELECT statement within another statement.


SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
Subquery Syntax

SELECT … FROM … WHERE ( )





non_subquery_operand op (subquery)
operand op [ANY|SOME|ALL] (subquery)
operand (NOT) IN (subquery)
(NOT) EXISTS (subquery)
SELECT … FROM (subquery) [AS] name …
Page 6
Subquery with Comparison Operators

SELECT … FROM … WHERE
non_subquery_operand op (subquery)


If expr is a value



op: =, >, <, >=, <=, <>, !=
subquery should be one column,
(possibly multiple rows, but only the value in the first row will be used.)
SELECT * FROM t1 WHERE column1 = (SELECT column2 FROM t2);
If expr is a scalar (row subqueries)



scalar: data with multi columns
subquery must return a single row (with the same number of column), or
empty result set
SELECT * FROM t1 WHERE (col1,col2) =
(SELECT col3, col4 FROM t2 WHERE id = 10);
Page 7
Subquery with Comparison Operators


Some queries with subquery can be replaced into JOIN operators.
But, you cannot do them with a JOIN


SELECT * FROM t1 WHERE column1 =
(SELECT MAX(column2) FROM t2);
SELECT * FROM t1 AS t WHERE 2 =
(SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);
Page 8
ANY(SOME), ALL


SELECT … FROM … WHERE
operand op [ANY|SOME|ALL] (subquery)
ANY



SOME



Return TRUE if the comparison is TRUE for ANY of the values in the column
that the subquery returns.
SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
An alias for ANY
Using SOME (just) helps ensure that everyone understands the true
meaning of the query.
ALL


Return TRUE if the comparison is TRUE for ALL of the values in the column
that the subquery returns.
SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
Page 9
IN, NOT IN

IN




an alias for = ANY
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
NOT IN



an alias for <> ALL
SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);
Page 10
EXISTS, NOT EXISTS

If a subquery return any rows:



EXISTS (subquery): TRUE
NOT EXISTS (subquery): FALSE
EXISTS

SELECT * FROM suppliers WHERE EXISTS
(SELECT * FROM orders
WHERE suppliers.supplier_id = orders.supplier_id);


Return suppliers there is at least one record in the order table.
NOT EXISTS

SELECT * FROM suppliers WHERE NOT EXISTS
(SELECT * FROM orders
WHERE suppliers.supplier_id = orders.supplier_id);

Return suppliers there are no records in the order table.
Page 11
Subqueries in the FROM Clause


SELECT ... FROM (subquery) [AS] name ...
SELECT AVG(sum_column1)
FROM
(SELECT COUNT(column1) AS sum_column1 FROM t1 GROUP BY
column1) AS t1;
1.
2.
3.
t1 is grouped by column1.
The number of rows for each group counted and stored at sum_column1.
The result is average of counted values.
Page 12
Practice Info


http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
위 사이트에서 코드 실행 후 SQL문 올려주세요.
Page 13
Practice DB – Northwest Sample DB
Truncated version of Northwest Sample DB
Page 14
Practice

1. UNION을 이용하여 Customer와
Supplier가 최소 1인 이상 존재하
는 모든 Country를 찾아라.

결과는 오름차순으로 정렬하여
출력하여라.
Page 15
Practice

2. UNION ALL이 포함된 subquery
를 이용하여 각 Country에 소속된
Customer와 Supplier의 수의 합이
몇명인지 구하여라.

결과는 포함 횟수의 내림차순으
로 출력하여라.
Page 16
Practice

3. Germany에 6회 이상 납품한 경
험이 있는 Shipper을 찾아라.

ShipperName을 오름차순으로 정
렬하여 출력한다.
Page 17
Practice

4. Supplier가 존재하지 않는 국가
에 사는 Customer을 아래 구문을
이용하여 찾는다.



4-1. NOT IN
4-2. NOT EXISTS
CustomerName, Country를
Country, Customer 순으로 정렬하
여 출력한다.
Page 18
Practice

5. 1회 Order에 10개 이상 취급 된
적이 있는 Product를 아래 구문을
이용하여 찾는다.



5-1. IN
5-2. EXISTS
ProductName을 오름차순으로 정
렬하여 출력한다.
Page 19
Practice

6. 각 Product가 가장 많이 팔린
Country를 구하여라.
(optional, 10% additional)




ProductName, Country, Sum of
Quantity를 ProductName 오름차
순으로 출력한다.
Hint: 최고 값을 찾기 위해 LIMIT 1
을 사용할 수 있음.
너무 느리지 않아야 할 것!
W3School에서는 ALL과 ANY를 지
원하지 않음.
Page 20
Practice


Due date: Today 23:59:59
연습 장소


http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
주의 사항.



6번 문제를 풀었을 경우 제출 시 포함 여부를 제목에 표시
1-5번 문제와 6번 문제를 따로 제출하여도 됨.
단 검사 받은 게시글을 수정하지 말 것.
Page 21