LESSON 3.
1b
98-364 Database Management Fundamentals
Select Complex Queries
LESSON 3.1b
98-364 Database Management Fundamentals
Lesson Overview
3.1b Select data. This objective may include but is not limited to:
queries to extract data from one table; extracting data by using
joins; combining result sets by using UNION and INTERSECT.
In this lesson, you will review:
Subqueries
UNIONS
JOINS
INTERSECTS
LESSON 3.1b
98-364 Database Management Fundamentals
Subqueries
• In Structured Query Language (SQL), a query can nest inside another
query
• There are three basic types of subqueries.
Type 1
Predicate—extended logical constructs in the WHERE (and HAVING)
clause(s) using the operators AND, OR, LIKE, BETWEEN, AS, and
TOP(LIMIT). Example:
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
SELECT subject
FROM class_info
WHERE teacher LIKE “Sm”
LESSON 3.1b
98-364 Database Management Fundamentals
Subqueries (Continued)
Type 2
Scalar—stand-alone queries that return a single value. Scalar subqueries
can be used in CASE expressions, WHERE clauses, ORDER BY clauses, and
SELECT clauses. Example:
SELECT column_name(s)
FROM table_name
WHERE variable_1 = (SELECT column_name
FROM table_name
WHERE column_name = variable_2)
LESSON 3.1b
98-364 Database Management Fundamentals
Subqueries (Continued)
Type 3
Table—queries nested in the FROM clause.
SELECT table_name_1
FROM table_name_1,
(SELECT column_name_2
FROM table_name_2
WHERE column_name_3 = variable_1)
WHERE table_name_1 .column_name_1 =
table_name_2.column_name_2
Syntax note: All subqueries must be enclosed in parentheses.
LESSON 3.1b
98-364 Database Management Fundamentals
UNION
The UNION clause combines the results of two SQL queries into a
single table of all matching rows. The two queries must have the same
number of columns and compatible data types to unite. Any duplicate
records are removed automatically unless UNION ALL is used.
Example:
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
NO duplicates allowed.
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
Duplicates allowed.
LESSON 3.1b
98-364 Database Management Fundamentals
JOIN
The INNER JOIN keyword returns rows when there is at least one match
in both tables. JOIN returns rows where the value in column_name in
table_name1 matches the value in column_name in table_name2.
These new rows will have columns from both table_name1 and
table_name2, except for column_name.
Example:
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Note: INNER JOIN is the same as JOIN.
LESSON 3.1b
98-364 Database Management Fundamentals
LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table_name1),
even if there are no matches in the right table (table_name2).
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
RIGHT JOIN
The RIGHT JOIN keyword returns all rows from the right table
(table_name2), even if there are no matches in the left table (table_name1).
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Note: In some databases, RIGHT JOIN is called RIGHT OUTER JOIN.
LESSON 3.1b
98-364 Database Management Fundamentals
FULL JOIN
The FULL JOIN keyword returns rows when there is a match in one of
the tables. The FULL JOIN returns value even if only one of the tables
has a value, unlike INNER JOIN, which must have a match in both
tables.
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LESSON 3.1b
98-364 Database Management Fundamentals
JOIN Types
JOIN—the INNER JOIN keyword returns rows when there is at least one
match in both tables. JOIN and INNER JOIN are the same.
LEFT JOIN—the LEFT JOIN keyword returns all rows from the left
table (table_name1), even if there are no matches in the right table
(table_name2).
RIGHT JOIN—the RIGHT JOIN keyword returns all rows from the
right table (table_name2), even if there are no matches in the left table
(table_name1).
FULL JOIN—the FULL JOIN keyword returns rows when there is a
match in one of the tables.
INTER
LESSON 3.1b
98-364 Database Management Fundamentals
SECT
INTERS
ECT
combine
s two or
more
SELECT
statemen
ts.
INTERS
ECT is
essential
ly the
same as
a
Boolean
AND
LESSON 3.1b
98-364 Database Management Fundamentals
Lesson Review
1. What is a subquery?
2. What is a JOIN?
3. What is the command that is used to keep duplicates in UNION
and INTERSECT commands?
