Write SELECT statements to access data from more than one table
View data that generally does not meet a join condition by using outer joins Join a table by using a self-join
Introduction to the SQL INNER JOIN clause
So far, you have learned how to use the SELECT statement to query data from a single table. However, the SELECT statement is not limited to query data from a single table. The SELECT statement can link multiple tables together.
The process of linking tables is called joining. SQL provides many kinds of joins such as inner join, left join, right join, full outer join, etc. This tutorial focuses on the inner join.
Suppose, you have two tables: A and B.
Table A has four rows: (1,2,3,4) and table B has four rows: (3,4,5,6)
When table A joins with table B using the inner join, you have the result set (3,4) that is the intersection of table A and table B.
See the following picture.
For each row in table A, the inner join clause finds the matching rows in table B. If a row is matched, it is included in the final result set.
1) Using SQL INNER JOIN to join two tables example
SELECT
first_name,
last_name,
employees.department_id,
departments.department_id,
department_name
FROM
employees
INNERJOIN
departments ON departments.department_id = employees.department_id
WHERE
employees.department_id IN (1 , 2, 3);
For each row in the employees table, the statement checks if the value of the department_id column equals the value of the department_id column in the departments table. If the condition
If the condition employees.department_id = departments.department_id is satisfied, the combined row that includes data from rows in both employees and departments tables are included in the result set.
Notice that both employees and departments tables have the same column name department_id, therefore we had to qualify the department_id column using the syntax table_name.column_name.
SQL INNER JOIN 3 tables example
Each employee holds one job while a job may be held by many employees. The relationship between the jobs table and the employees table is one-to-many.
The following database diagram illustrates the relationships between employees, departments and jobs tables:
The following query uses the inner join clauses to join 3 tables: employees, departments, and jobs to get the first name, last name, job title, and department name of employees who work in department id 1, 2, and 3.
SELECT
first_name,
last_name,
job_title,
department_name
FROM
employees e
INNERJOIN departments d ON d.department_id = e.department_id
INNERJOIN jobs j ON j.job_id = e.job_id
WHERE
e.department_id IN (1, 2, 3);
In the previous tutorial, you learned about the inner join that returns rows if there is, at least, one row in both tables that matches the join condition. The inner join clause eliminates the rows that do not match with a row of the other table.
The left join, however, returns all rows from the left table whether or not there is a matching row in the right table.
Suppose we have two tables A and B. The table A has four rows 1, 2, 3 and 4. The table B also has four rows 3, 4, 5, 6.
When we join table A with table B, all the rows in table A (the left table) are included in the result set whether there is a matching row in the table B or not.
SELECT
c.country_name,
c.country_id,
l.country_id,
l.street_address,
l.city
FROM
countries c
LEFTJOIN locations l ON l.country_id = c.country_id
WHERE
c.country_id IN ('US', 'UK', 'CN')
The condition in the WHERE clause is applied so that the statement only retrieves the data from the US, UK, and China rows.
Because we use the LEFT JOIN clause, all rows that satisfy the condition in the WHERE clause of the countries table are included in the result set.
For each row in the countries table, the LEFT JOIN clause finds the matching rows in the locations table.
If at least one matching row found, the database engine combines the data from columns of the matching rows in both tables.
In case there is no matching row found e.g., with the country_id CN, the row in the countries table is included in the result set and the row in the locations table is filled with NULL values.
Because non-matching rows in the right table are filled with the NULL values, you can apply the LEFT JOIN clause to miss-match rows between tables.
For example, to find the country that does not have any locations in the locations table, you use the following query:
SELECT
country_name
FROM
countries c
LEFTJOIN locations l ON l.country_id = c.country_id
WHERE
l.location_id ISNULLORDERBY
country_name;
In theory, a full outer join is the combination of a left join and a right join. The full outer join includes all rows from the joined tables whether or not the other table has the matching row.
If the rows in the joined tables do not match, the result set of the full outer join contains NULL values for every column of the table that lacks a matching row. For the matching rows, a single row that has the columns populated from the joined table is included in the result set.
The following statement illustrates the syntax of the full outer join of two tables:
SELECT column_list
FROM A
FULLOUTERJOIN B ON B.n = A.n;
The following Venn diagram illustrates the full outer join of two tables.
SQL FULL OUTER JOIN examples
Let’s take an example of using the FULL OUTER JOIN clause to see how it works.
First, create two new tables: baskets and fruits for the demonstration. Each basket stores zero or more fruits and each fruit can be stored in zero or one basket.
Third, the following query returns each fruit that is in a basket and each basket that has a fruit, but also returns each fruit that is not in any basket and each basket that does not have any fruit.
SELECT
basket_name,
fruit_name
FROM
fruits
FULLOUTERJOIN baskets ON baskets.basket_id = fruits.basket_id;
A cross join is a join operation that produces the Cartesian product of two or more tables.
In Math, a Cartesian product is a mathematical operation that returns a product set of multiple sets.
For example, with two sets A {x,y,z} and B {1,2,3}, the Cartesian product of A x B is the set of all ordered pairs (x,1), (x,2), (x,3), (y,1) (y,2), (y,3), (z,1), (z,2), (z,3).
The following picture illustrates the Cartesian product of A and B:
Similarly, in SQL, a Cartesian product of two tables A and B is a result set in which each row in the first table (A) is paired with each row in the second table (B). Suppose the A table has n rows and the B table has m rows, the result of the cross join of the A and B tables have n x m rows.
The following picture illustrates the result of the cross join between the table A and table B. In this illustration, the table A has three rows 1, 2 and 3 and the table B also has three rows x, y and z. As the result, the Cartesian product has nine rows:
The company can distribute goods via various channels such as wholesale, retail, eCommerce, and TV shopping. The following statement inserts sales channels into the sales_channel table:
To find the all possible sales channels that a sales organization can have, you use the CROSS JOIN to join the sales_organization table with the sales_channel table as follows:
SELECT
sales_org,
channel
FROM
sales_organization
CROSSJOIN sales_channel;
Sometimes, it is useful to join a table to itself. This type of join is known as the self-join.
We join a table to itself to evaluate the rows with other rows in the same table. To perform the self-join, we use either an inner join or left join clause.
Because the same table appears twice in a single query, we have to use the table aliases. The following statement illustrates how to join a table to itself.
SQL self-join examples
See the following employees table.
The manager_id column specifies the manager of an employee. The following statement joins the employees table to itself to query the information of who reports to whom.
SELECT
e.first_name || ' ' || e.last_name AS employee,
m.first_name || ' ' || m.last_name AS manager
FROM
employees e
INNERJOIN
employees m ON m.employee_id = e.manager_id
ORDERBY manager;
The president does not have any manager. In the employees table, the manager_id of the row that contains the president is NULL.
Because the inner join clause only includes the rows that have matching rows in the other table, therefore the president did not show up in the result set of the query above.
To include the president in the result set, we use the LEFT JOIN clause instead of the INNER JOIN clause as the following query.
SELECT
e.first_name || ' ' || e.last_name AS employee,
m.first_name || ' ' || m.last_name AS manager
FROM
employees e
LEFTJOIN
employees m ON m.employee_id = e.manager_id
ORDERBY manager;
This solution has two problems. To start with, you have looked at the departments table to check which department belongs to the location 1700. However, the original question was not referring to any specific departments; it referred to the location 1700.
Because of the small data volume, you can get a list of department easily. However, in the real system with high volume data, it might be problematic.
Another problem was that you have to revise the queries whenever you want to find employees who locate in a different location.
A much better solution to this problem is to use a subquery. By definition, a subquery is a query nested inside another query such as SELECT, INSERT, UPDATE, or DELETE statement. In this tutorial, we are focusing on the subquery used with the SELECT statement.
In this example, you can rewrite combine the two queries above as follows:
SELECT
employee_id, first_name, last_name
FROM
employees
WHERE
department_id IN (SELECT
department_id
FROM
departments
WHERE
location_id = 1700)
ORDERBY first_name , last_name;
The query placed within the parentheses is called a subquery. It is also known as an inner query or inner select. The query that contains the subquery is called an outer query or an outer select.
To execute the query, first, the database system has to execute the subquery and substitute the subquery between the parentheses with its result – a number of department id located at the location 1700 – and then executes the outer query.
Let’s take some examples of using the subqueries to understand how they work.
SQL subquery with the IN or NOT IN operator
In the previous example, you have seen how the subquery was used with the IN operator. The following example uses a subquery with the NOT IN operator to find all employees who do not locate at the location 1700:
SELECT
employee_id, first_name, last_name
FROM
employees
WHERE
department_id NOTIN (SELECT
department_id
FROM
departments
WHERE
location_id = 1700)
ORDERBY first_name , last_name;
In this example, the subquery returns the highest salary of all employees and the outer query finds the employees whose salary is equal to the highest one.
The following statement finds all employees who salaries are greater than the average salary of all employees:
SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary > (SELECTAVG(salary)
FROM
employees);
In this example, first, the subquery returns the average salary of all employees. Then, the outer query uses the greater than operator to find all employees whose salaries are greater than the average.
SQL subquery with the EXISTS or NOT EXISTS operator
The EXISTS operator checks for the existence of rows returned from the subquery. It returns true if the subquery contains any rows. Otherwise, it returns false.
The following example finds all departments which have at least one employee with the salary is greater than 10,000:
SELECT
department_name
FROM
departments d
WHEREEXISTS( SELECT1FROM
employees e
WHERE
salary > 10000AND e.department_id = d.department_id)
ORDERBY department_name;
The UNION operator combines result sets of two or moreSELECTstatements into a single result set. The following statement illustrates how to use the UNION operator to combine result sets of two queries:
To use the UNION operator, you write the dividual SELECT statements and join them by the keyword UNION.
The columns returned by the SELECT statements must have the same or convertible data type, size, and be the same order.
The database system processes the query by executing two SELECT statements first. Then, it combines two individual result sets into one and eliminates duplicate rows. To eliminate the duplicate rows, the database system sorts the combined result set by every column and scans it for the matching rows located next to one another.
To retain the duplicate rows in the result set, you use the UNION ALL operator.
Suppose, we have two result sets A(1,2) and B(2,3). The following picture illustrates A UNION B:
And the following picture illustrates A UNION ALL B
The union is different from the join that the join combines columns of multiple tables while the union combines rows of the tables.
The database system performs the following steps:
First, execute each SELECT statement individually.
Second, combine result sets and remove duplicate rows to create the combined result set.
Third, sort the combined result set by the column specified in the ORDER BY clause.
In practice, we often use the UNION operator to combine data from different tables. See the following employees and dependents tables:
The following statement uses the UNION operator to combine the first name and last name of employees and dependents.
SELECT
first_name,
last_name
FROM
employees
UNIONSELECT
first_name,
last_name
FROM
dependents
ORDERBY
last_name;
In this tutorial, you have learned how to use the UNION operator to combine two or more result sets from multiple queries.
Introduction to SQL INTERSECT operator
The INTERSECT operator is a set operator that returns distinct rows of two or more result sets from SELECT statements.
Suppose, we have two tables: A(1,2) and B(2,3).
The following picture illustrates the intersection of A & B tables.
The purple section is the intersection of the green and blue result sets.
Like the UNION operator, the INTERSECT operator removes the duplicate rows from the final result set.
To use the INTERSECT operator, the columns of the SELECT statements must follow the rules:
The data types of columns must be compatible.
The number of columns and their orders in the SELECT statements must be the same.
SQL INTERSECT with ORDER BY example
To sort the result set returned by the INTERSECT operator, you place the ORDER BY clause at the end of all statements.
For example, the following statement applies the INTERSECT operator to the A and B tables and sorts the combined result set by the id column in descending order.
SELECTidFROM
a
INTERSECTSELECTidFROM
b
ORDERBYidDESC;
Emulate SQL INTERSECT operator using INNER JOIN clause
Most relational database system supports the INTERSECT operator such as Oracle Database, Microsoft SQL Server, PostgreSQL, etc. However, some database systems do not provide the INTERSECT operator like MySQL.
To emulate the SQL INTERSECT operator, you can use the INNER JOIN clause as follows:
It returns the rows in the A table that have matching rows in the B table, which produces the same result as the INTERSECT operator.
Introduction to SQL MINUS operator
Besides the UNION, UNION ALL, and INTERSECT operators, SQL provides us with the MINUS operator that allows you to subtract one result set from another result set.
To use the MINUS operator, you write individual SELECT statements and place the MINUS operator between them. The MINUS operator returns the unique rows produced by the first query but not by the second one.
The following picture illustrates the MINUS operator.
To make the result set, the database system performs two queries and subtracts the result set of the first query from the second one.
In order to use the MINUS operator, the columns in the SELECT clauses must match in number and must have the same or, at least, convertible data type.
We often use the MINUS operator in ETL. An ETL is a software component in data warehouse system. ETL stands for Extract, Transform, and Load. ETL is responsible for loading data from the source systems into the data warehouse system.
After complete loading data, we can use the MINUS operator to make sure that the data has been loaded fully by subtracting data in target system from the data in the source system.
Each employee has zero or more dependents while each dependent depends on one and only one employees. The relationship between the dependents and employees is the one-to-many relationship.
The employee_id column in the dependents table references to the employee_id column in the employees table.
You can use the MINUS operator to find the employees who do not have any dependents. To do this, you subtract the employee_id result set in the employees table from the employee_id result set in the dependents table.
The following query illustrates the idea:
SELECT
employee_id
FROM
employees
MINUSSELECT
employee_id
FROM
dependents;
SQL MINUS with ORDER BY example
To sort the result set returned by the MINUS operator, you place the ORDER BY clause at the end of the last SELECT statement.
For example, to sort the employees who do not have any dependents, you use the following query:
SELECT
employee_id
FROM
employees
MINUSSELECT
employee_id
FROM
dependents
ORDERBY employee_id;
Use a SET operator to combine multiple queries into a single query Control the order of rows returned
Data Manipulation Statements
Describe each DML statement
Insert rows into a table
Change rows in a table by the UPDATE statement
Delete rows from a table with the DELETE statement
Save and discard changes with the COMMIT and ROLLBACK statements Explain read consistency
Use of DDL Statements to Create and Manage Tables
Categorize the main database objects
Review the table structure
List the data types available for columns
Create a simple table
Decipher how constraints can be created at table creation
Describe how schema objects work
Other Schema Objects
Create a simple and complex view
Retrieve data from views
Create, maintain, and use sequences
Create and maintain indexes
Create private and public synonyms
Control User Access
Differentiate system privileges from object privileges Create Users
Grant System Privileges
Create and Grant Privileges to a Role
Change Your Password
Grant Object Privileges
How to pass on privileges?
Revoke Object Privileges
Management of Schema Objects
Add, Modify and Drop a Column
Add, Drop and Defer a Constraint
How to enable and Disable a Constraint?
Create and Remove Indexes
Create a Function-Based Index
Perform Flashback Operations
Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP Query External Tables
Manage Objects with Data Dictionary Views
Explain the data dictionary
Use the Dictionary Views
USER_OBJECTS and ALL_OBJECTS Views
Table and Column Information
Query the dictionary views for constraint information
Query the dictionary views for view, sequence, index, and synonym information Add a comment to a table
Query the dictionary views for comment information
Manipulate Large Data Sets
Use Subqueries to Manipulate Data
Retrieve Data Using a Subquery as Source
Insert Using a Subquery as a Target
Usage of the WITH CHECK OPTION Keyword on DML Statements List the types of Multitable INSERT Statements
Use Multitable INSERT Statements
Merge rows in a table
Track Changes in Data over a period of time
Data Management in Different Time Zones
Time Zones
CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP þÿCompare Date and Time in a Session s Time Zone DBTIMEZONE and SESSIONTIMEZONE
Difference between DATE and TIMESTAMP
INTERVAL Data Types
Use EXTRACT, TZ_OFFSET, and FROM_TZ
Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL
Retrieve Data Using Sub-queries
Multiple-Column Subqueries
Pairwise and Nonpairwise Comparison
Scalar Subquery Expressions
Solve problems with Correlated Subqueries
Update and Delete Rows Using Correlated Subqueries The EXISTS and NOT EXISTS operators
Invoke the WITH clause
The Recursive WITH clause
Regular Expression Support
Use the Regular Expressions Functions and Conditions in SQL Use Meta Characters with Regular Expressions
Perform a Basic Search using the REGEXP_LIKE function Find patterns using the REGEXP_INSTR function Extract Substrings using the REGEXP_SUBSTR function Replace Patterns Using the REGEXP_REPLACE function Usage of Sub-Expressions with Regular Expression Support
Comments
Post a Comment