How to obtain data from multiple tables using SQL
Sometimes you need to use data from more than one table. This article will explore different types of joins with simple examples
SQL is a very popular query language used by almost everyone has to work with data.
Every business relies on data analytics and its so common that the organizations use SQL to gain insights and recommendations from their data assets.
Industry-accepted committees are the American National Standards Institute (ANSI) and the International Standards Organization (ISO). Both ANSI and ISO have accepted SQL as the standard language for relational databases.
SQL is the language used to communicate with the database to access, manipulate, and control data.
After reading this article, you will have fair understanding on different type of joins and you will be able to :
Write SELECT statements to access data from more than one table using joins
View data that generally does not meet a join condition by using outer joins
Join a table to itself by using a self join
Cartesian product is nothing but a cross-join which returns all the rows in all the tables listed in a query
It is formed when:
– A join condition is omitted
– A join condition is invalid
– All rows in the first table are joined to all rows in the second table
To avoid a Cartesian product, always include a valid join condition in a WHERE clause.
The CROSS JOIN clause produces the cross product of two tables. Which is same as a Cartesian product between the two tables.
All rows (107 rows) from the EMPLOYEES table are joined with all rows (27 rows) in the DEPARTMENTS table, thereby generating 2889 rows in the output.
Note: Cartesian products are useful for some tests when you need to generate a large number of rows to simulate a reasonable amount of data.
The relationship between the EMPLOYEES and DEPARTMENTS tables is an inner join. That is, values in the DEPARTMENT_ID column on both tables must be equal.
Frequently, This type of join involves primary and foreign key constraints.
Inner Join are also called simple joins or Equal joins
The DEPARTMENT_ID column is common to both tables, it must be prefixed by the table name to avoid ambiguity.
All rows (107 rows) from the EMPLOYEES table are joined with all rows (27 rows) in the DEPARTMENTS table, thereby generating 106 matched rows in the output.
Joining More than Two Tables
Sometimes you may need to join more than two tables.
For example, to display the employee last name, the department name, and the city for each employee, you have to join the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables
To join n tables together, you need a minimum of n-1 join conditions.
For example, to join three tables, a minimum of two joins are required.
A non-inner or non-equal join is a join condition containing something other than an JOIN and ON or equality operator.
The relationship between the EMPLOYEES table and the JOB_GRADES table has an example of a non-inner or equal join. A relationship between the two tables is that the SALARY column in the EMPLOYEES table must be between the values in the LOWEST_SALARY and HIGHEST_SALARY columns of the JOB_GRADES table.
The relationship is obtained using an operator other than JOIN and ON or equals (=).
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
LEFT OUTER JOIN
This query retrieves all rows in the EMPLOYEES table, which is the left table even if there is no match in the DEPARTMENTS table.
In the above output, you can see as last name Grant doesn’t belong to any department.
RIGHT OUTER JOIN
This query retrieves all rows in the DEPARTMENTS table, which is the right table even if there is no match in the EMPLOYEES table.
In the above output, you can see as many departments doesn’t have any employees.
FULL OUTER JOIN
This query retrieves all rows in the EMPLOYEES table, even if there is no match in the DEPARTMENTS table. It also retrieves all rows in the DEPARTMENTS table, even if there is no match in the EMPLOYEES table.
The output of above query combines both left and right outer join query’s output.
Sometimes you need to join a table to itself. Which means that you use the same table on both sides.
To find the name of each employee’s manager, you need to join the EMPLOYEES table to itself, or perform a self join.
Two tables in the FROM clause, there are two aliases, namely worker and manager, for the same table, EMPLOYEES.
The NATURAL JOIN clause is based on all columns in the two tables that have the same name
It selects rows from the two tables that have equal values in all matched columns. If the columns having the same names have different data types, an error is returned.
The natural join can also be written as an inner or equal join
Joins with the USING Clause
If several columns have the same names but the data types do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equal join.
Use the USING clause to match only one column when more than one column matches.
I have tested all of the above query’s on Snowflake database and these must be executed also in Oracle as i referenced some of the examples from the below websites.
A join is a query that combines rows from two or more tables, views, or materialized views. Oracle Database performs a…
The JOIN operations, which are among the possible TableExpressions in a FROM clause, perform joins between two tables…
JOIN - Snowflake Documentation
For each row of , a row is produced for each row of that matches according to the subclause. (Note that you can also…
The following is the URL i have used to generate sample data for the examples i have used in this article.
CREATE TABLE employees ( employee_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) CONSTRAINT…
I have added the modified DDL and DML script to my GitHub repo.
HR_Sample_Data/hr_sample_data.sql at main · vvgsrk/HR_Sample_Data
HR Sample Data contains tables like regions, countries, locations, departments, jobs, employees, job_history and…
Hope this information helps you somehow. Thank you for reading.
Disclaimer: The opinions expressed in my articles are my own and will not necessarily reflect those of my employer (past or present) or indeed any client I have worked with.