The SELECTstatement is used to query Oracle Database table data, whether from a single table, or when used to query multiple tables. To query multiple tables, the tables are specified in the FROMclause, either with or without a JOINclause separating the tables.
A SELECTstatement that queries more than one table is called a join. Typically, a join condition is also specified for each join, that specifies the column/s that are compared from the two tables being joined and a comparison operator. When the join condition evaluates to TRUE, data from the corresponding rows in the two tables being joined is combined and returned. A WHEREcondition could be used additionally to filter data. Joins could be made over table views, but we will only discuss table joins to query multiple tables.
What are the different types of table joins?
Before covering how to query multiple tables, we must first cover the different types of table joins. The three main types of joins are:
- INNER JOIN
- LEFT [OUTER] JOIN
- RIGHT [OUTER] JOIN
The square parentheses around theOUTERindicate that it is optional in the JOINclause.
An inner join joins two tables and returns only the rows of data from the two tables for which the join condition can find matching rows of data, or records, in both the tables. An inner join is represented in Figure 1 in which each circle is a table, and the circle intersection represents data for which the join condition can find matching rows of data, or records, in both the tables. The order of the tables in an INNER JOIN is not significant, implying that if the tables on the left and right side of the INNER JOIN clause are switched the result would be the same.
Figure 1.
A left outer join between two tables returns the data from the two tables for which the join condition is able to find matching rows of data, or records, in both the tables s for an inner join. It additionally returns the rows of data from the table on the left of the LEFT OUTER JOIN clause for which the join condition is not able to find matching rows of data, or records, in the table on the right of the LEFT OUTER JOIN clause. Null values are returned for the non-existing data in the right table. A left outer join is represented in Figure 2. The order of the tables in a LEFT OUTER JOIN is significant, implying that if the tables on the left and right side of the LEFT OUTER JOIN clause are switched, the result may be different.
Figure 2.
A right outer join between two tables returns the data from the two tables for which the join condition is able to find matching rows of data, or records, in both the tables as for an inner join.It additionally returns the rows of data from the table on the right of the RIGHT OUTER JOINclause for which the join condition is not able to find matching rows of data, or records, in the table on the left of the RIGHT OUTER JOIN clause. Null values are returned for the non-existing data in the left table. A right outer join is represented in Figure 3. The order of the tables in a RIGHT OUTER JOINis significant, implying that if the tables on the left and right side of the RIGHT OUTER JOINclause are switched, the result may be different.
Figure 3.
A full outer join could also be made with the FULL OUTER JOINclause. A full outer join between two tables returns the data from the two tables for which the join condition is able to find matching rows of data, or records, in both the tables as for an inner join. It additionally returns the rows of data from the two tables for which the join condition is not able to find matching rows of data, or records. Null values are returned for the non-existing data.
Sample Tables
As a preliminary setup to query multiple tables, install Toad for Oracle, create an instance of Oracle Autonomous Database 19c (or other) and connect to the database instance, all of which is discussed in How to download Toad for Oracle.
Oracle Autonomous Database provides sample tables that are used in this article. The first sample table is SH.PRODUCTS(Figure 4) in the SH(Sales History) schema for products data.
Figure 4.
The second sample table is SH.CUSTOMERS (Figure 5) for customers data.
Figure 5.
The third sample table is SH.SALES (Figure 6) for sales data.
Figure 6.
Toad for Oracle provides code assist, where if the schema name is typed in a SQL Editor, the tables in the schema get listed for selection in a popup. As an example, if SH.is typed, the tables in the SHschema get listed in a popup. Another code assist feature is that if the table name is typed in a SQL Editor, the columns in the table get listed for selection in a popup. As an example, if SH.PRODUCTS.is typed, the columns in the SH.PRODUCTStable get listed in a popup.
Simplest multi-table join – Cartesian product
If no join condition is specified in a multi-table query, a cartesian product of the two tables is returned. A cartesian product combines each row of data in one table with each row of data in the other table, producing several rows in the result. An example of a cartesian product SELECTstatement is:
SELECT SH.PRODUCTS.PROD_ID SH.SALES.QUANTITY_SOLD
FROM SH.PRODUCTS, SH.SALES;
Toad for Oracle SQL Editor displays a message to “Avoid cartesian queries..” (Figure 7) and use a WHEREclause or an INNER JOINto specify a join condition.
Figure 7.
What is an equijoin?
An equijoin performs a join based on the equality of the column values in the two tables being joined; the join condition uses only the equality (=) comparison operator. An example equijoin query is:
SELECT cust_first_name, cust_last_name, prod_id, quantity_sold
FROM SH.CUSTOMERS, SH.SALES
WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID AND
ORDER BY SH.CUSTOMERS.CUST_ID;
Using an INNER JOIN
An inner join may be made using the INNER JOINclause with the join condition specified in the ONclause. An example:
SELECT cust_first_name, cust_last_name, prod_id, quantity_sold
FROM SH.CUSTOMERS INNER JOIN SH.SALES
ON SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID
WHERE ROWNUM<=10;
CUST_FIRST_NAME CUST_LAST_NAME PROD_ID
——————– —————————————- ———-
QUANTITY_SOLD
————-
Abigail Ruddy 23
1
Abigail Ruddy 24
1
Abigail Ruddy 24
1
Abigail Ruddy 24
1
Abigail Ruddy 24
1
Abigail Ruddy 25
1
Abigail Ruddy 25
CUST_FIRST_NAME CUST_LAST_NAME PROD_ID
——————– —————————————- ———-
QUANTITY_SOLD
————-
1
Abigail Ruddy 25
1
Abigail Ruddy 26
1
Abigail Ruddy 29
1
10 rows selected.
An inner join is also called a simple join because it returns data for records matched in both the tables. Other query clauses may be included as for any other SELECTstatement. As an example, include the ORDER BYclause as follows:
SELECT SH.CUSTOMERS.CUST_ID, PROD_ID, quantity_sold
FROM SH.SALES
INNER JOIN SH.CUSTOMERS
ON SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID
WHERE ROWNUM<=10
ORDER BY SH.CUSTOMERS.CUST_ID;
CUST_ID PROD_ID QUANTITY_SOLD
———- ———- ————-
524 13 1
1949 13 1
2128 13 1
3212 13 1
3375 13 1
5204 13 1
7082 13 1
9316 13 1
12782 13 1
13869 13 1
10 rows selected.
The inner join example as run in Toad for Oracle is shown in Figure 8.
Figure 8.
An inner join may be made without the INNER JOINclause with the tables to be joined specified in the FROMclause, and the join condition specified in the WHEREclause. The preceding example without the INNER JOINand ONclauses is as follows:
SELECT SH.CUSTOMERS.CUST_ID, PROD_ID, quantity_sold
FROM SH.SALES, SH.CUSTOMERS
WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID
AND ROWNUM<=10
ORDER BY SH.CUSTOMERS.CUST_ID;
Using a subquery
A subquery may be used in a multi-table query, and in fact is necessary at times. An example multi-table query that includes a subquery:
SELECT cust_first_name, cust_last_name, prod_id, quantity_sold
FROM (SELECT * FROM SH.CUSTOMERS, SH.SALES
WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID)
WHERE ROWNUM<=10;
CUST_FIRST_NAME CUST_LAST_NAME PROD_ID
——————– —————————————- ———-
QUANTITY_SOLD
————-
Abigail Ruddy 23
1
Abigail Ruddy 24
1
Abigail Ruddy 24
1
Abigail Ruddy 24
1
Abigail Ruddy 24
1
Abigail Ruddy 25
1
Abigail Ruddy 25
CUST_FIRST_NAME CUST_LAST_NAME PROD_ID
——————– —————————————- ———-
QUANTITY_SOLD
————-
1
Abigail Ruddy 25
1
Abigail Ruddy 26
1
Abigail Ruddy 29
1
10 rows selected.
Using a LEFT OUTER join
A left outer join can only be made with the LEFT OUTER JOINclause. An alternative query using the JOINclause could be used to generate a query that is equivalent to a LEFT OUTER JOINquery but would be too verbose. An example is an INNER JOINcombined in a UNIONwith a query on the left table only, with a NOT EXISTSsubquery. An example left outer join:
SELECT SH.CUSTOMERS.CUST_ID, PROD_ID, quantity_sold
FROM SH.SALES
LEFT OUTER JOIN SH.CUSTOMERS
ON SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID
WHERE ROWNUM<=10
ORDER BY SH.CUSTOMERS.CUST_ID;
CUST_ID PROD_ID QUANTITY_SOLD
———- ———- ————-
524 13 1
1949 13 1
2128 13 1
3212 13 1
3375 13 1
5204 13 1
7082 13 1
9316 13 1
12782 13 1
13869 13 1
Using a RIGHT OUTER JOIN
A right outer join can only be made with the RIGHT OUTER JOINclause. An alternative query using the JOINclause could be used to generate a query that is equivalent to a RIGHT OUTER JOINquery but would be too verbose. An example right outer join:
SELECT SH.CUSTOMERS.CUST_ID, PROD_ID, quantity_sold
FROM SH.SALES
RIGHT OUTER JOIN SH.CUSTOMERS
ON SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID
WHERE ROWNUM<=10
ORDER BY SH.CUSTOMERS.CUST_ID;
CUST_ID PROD_ID QUANTITY_SOLD
———- ———- ————-
3228 24 1
3228 23 1
3228 24 1
3228 24 1
3228 26 1
3228 24 1
3228 25 1
3228 25 1
3228 25 1
49671
10 rows selected.
The right outer join example as run in Toad for Oracle is shown in Figure 9.
Figure 9.
Using a FULL OUTER JOIN
A full outer join can only be made with the FULL OUTER JOINclause. An alternative query using the JOIN clause may exist, and could be used to generate a query that is equivalent to a FULL OUTER JOINquery, but would be too verbose. An example full outer join:
SELECT SH.CUSTOMERS.CUST_ID, PROD_ID, quantity_sold
FROM SH.SALES
FULL OUTER JOIN SH.CUSTOMERS
ON SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID
WHERE ROWNUM<=10
ORDER BY SH.CUSTOMERS.CUST_ID;
CUST_ID PROD_ID QUANTITY_SOLD
———- ———- ————-
524 13 1
1949 13 1
2128 13 1
3212 13 1
3375 13 1
5204 13 1
7082 13 1
9316 13 1
12782 13 1
13869 13 1
10 rows selected.
Using the USING clause
A multi-table query may include the USINGclause to specify the join condition for an equijoin when using an explicit JOINclause. It is an alternative to using the more verbose ONclause, when all that the join condition is comparing is the equality of two columns for which the column name is the same in the two tables. Specify the column/s to use to test for equality with the USINGclause. An example:
SELECT CUST_ID, PROD_ID, quantity_sold
FROM SH.SALES
LEFT OUTER JOIN SH.CUSTOMERS
USING (CUST_ID)
WHERE ROWNUM<=10
ORDER BY CUST_ID;
CUST_ID PROD_ID QUANTITY_SOLD
———- ———- ————-
524 13 1
1949 13 1
2128 13 1
3212 13 1
3375 13 1
5204 13 1
7082 13 1
9316 13 1
12782 13 1
13869 13 1
10 rows selected.
The USINGclause example as run in Toad for Oracle is shown in Figure 10.
Figure 10.
Using the PARTITION BY clause
The PARTITION BYclause may be used in a JOINto partition data by a column. As an example, partition an inner join using the SH.PRODUCTS.PROD_ID column:
SELECT SH.SALES.PROMO_ID, SH.PRODUCTS.PROD_ID, SH.SALES.QUANTITY_SOLD
FROM SH.PRODUCTS PARTITION BY (SH.PRODUCTS.PROD_ID) INNER JOIN SH.SALES
ON SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID
WHERE SH.SALES.PROMO_ID < 35;
PROMO_ID PROD_ID QUANTITY_SOLD
———- ———- ————-
33 21 1
33 21 1
33 21 1
33 21 1
33 21 1
33 21 1
33 21 1
33 21 1
33 21 1
33 21 1
33 21 1
33 26 1
33 26 1
33 26 1
33 26 1
33 26 1
33 26 1
33 26 1
33 26 1
33 26 1
33 27 1
PROMO_ID PROD_ID QUANTITY_SOLD
———- ———- ————-
33 27 1
33 27 1
33 27 1
33 27 1
33 27 1
33 27 1
33 27 1
33 27 1
33 30 1
33 30 1
33 30 1
33 30 1
33 30 1
33 30 1
33 30 1
33 30 1
33 30 1
33 30 1
33 35 1
33 35 1
33 35 1
PROMO_ID PROD_ID QUANTITY_SOLD
———- ———- ————-
33 35 1
33 35 1
33 35 1
33 35 1
33 35 1
33 35 1
33 40 1
33 40 1
33 40 1
33 40 1
33 40 1
33 40 1
33 46 1
33 46 1
33 46 1
33 46 1
33 46 1
33 46 1
33 46 1
33 46 1
33 46 1
PROMO_ID PROD_ID QUANTITY_SOLD
———- ———- ————-
33 46 1
33 46 1
33 48 1
33 48 1
33 48 1
33 48 1
33 48 1
33 48 1
33 48 1
33 48 1
33 48 1
33 116 1
33 116 1
33 116 1
33 116 1
33 116 1
33 116 1
33 116 1
33 128 1
33 128 1
33 128 1
PROMO_ID PROD_ID QUANTITY_SOLD
———- ———- ————-
33 128 1
33 128 1
33 128 1
33 128 1
33 147 1
33 147 1
33 147 1
33 147 1
33 147 1
33 147 1
33 147 1
33 147 1
33 147 1
33 147 1
33 147 1
33 147 1
The PARTITION BYexample as run in Toad for Oracle is shown in Figure 11.
Figure 11.
Using an Antijoin
Antijoin is just the opposite of a join. It returns rows data from the first data set or table for which a matching row of data is not found in the subquery data set or table. An antijoin uses the NOT INor NOT EXISTSclause. An antijoin is not really a join, as it returns data from the first table only, but is still a query over multiple tables. An example:
SELECT SH.PRODUCTS.PROD_ID FROM SH.PRODUCTS
WHERE PROD_ID NOT IN
(SELECT PROD_ID FROM SH.SALES
WHERE quantity_sold > 10)
AND ROWNUM<=10
ORDER BY PROD_ID;
PROD_ID
———-
14
23
27
36
113
124
125
127
134
135
10 rows selected.
The antijoin example as run in Toad for Oracle is shown in Figure 12.
Figure 12.
Using a semijoin
A semijoin returns rows data from the first data set or table for which a matching row of data is also found in the subquery data set or table. A semijoin uses the IN or the EXISTSclause. A semijoin is not really a join, as it returns data from the first table only, but is still a query over multiple tables. An example:
SELECT PROD_ID FROM SH.PRODUCTS
WHERE EXISTS
(SELECT PROD_ID FROM SH.SALES
WHERE SH.PRODUCTS.PROD_ID = SH.SALES.PROD_ID
AND SH.SALES.QUANTITY_SOLD = 1)
ORDER BY PROD_ID;
PROD_ID
———-
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
PROD_ID
———-
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
113
114
115
116
117
118
PROD_ID
———-
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
PROD_ID
———-
140
141
142
143
144
145
146
147
148
72 rows selected.
The semijoin example as run in Toad for Oracle is shown in Figure 13.
Figure 13.
Try Toad free for 30 days.Already in a trial? Talk to sales or buy now online. Already a loyal fan of Toad for Oracle? Renew now. |
Three table join
A join could be made over more than two tables. When querying more than two tables, only two tables are joined at a time, and the order in which the two tables to be joined first is determined by an optimizer, or parentheses if provided. The result of the first join is joined with the third table, and so on until all tables are joined.
An inner join over three tables could be made without the INNER JOINclause with the tables specified in the FROM clause and the join condition specified in the WHERE clause. An example:
SELECT SH.SALES.PROMO_ID, SH.PRODUCTS.PROD_ID, SH.CUSTOMERS.CUST_FIRST_NAME, SH.SALES.QUANTITY_SOLD
FROM SH.PRODUCTS,
SH.CUSTOMERS,
SH.SALES
WHERE SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID
AND SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID
AND SH.SALES.PROMO_ID < 35;
PROMO_ID PROD_ID CUST_FIRST_NAME QUANTITY_SOLD
———- ———- ——————– ————-
33 30 Cole 1
33 21 Nicholas 1
33 46 Nicholas 1
33 128 Nicholas 1
33 30 Joshua 1
33 46 Joshua 1
33 128 Joshua 1
33 147 Joshua 1
33 30 Cole 1
33 46 Cole 1
33 128 Cole 1
33 147 Cole 1
33 21 Cole 1
33 147 Cole 1
33 30 Madison 1
33 27 Brayden 1
33 30 Brayden 1
33 40 Brayden 1
33 46 Brayden 1
33 116 Brayden 1
33 128 Brayden 1
PROMO_ID PROD_ID CUST_FIRST_NAME QUANTITY_SOLD
———- ———- ——————– ————-
33 147 Brayden 1
33 21 Hayden 1
33 26 Hayden 1
33 48 Hayden 1
33 128 Hayden 1
33 46 Connor 1
33 128 Connor 1
The three-table example as run in Toad for Oracle is shown in Figure 14.
Figure 14.
The same three-table join could be made using the INNER JOINand ONclauses as follows:
SELECT SH.SALES.PROMO_ID, SH.PRODUCTS.PROD_ID, SH.CUSTOMERS.CUST_FIRST_NAME, SH.SALES.QUANTITY_SOLD
FROM SH.PRODUCTS INNER JOIN SH.SALES
ON SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID
INNER JOIN SH.CUSTOMERS
ON SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID
WHERE SH.SALES.PROMO_ID < 35;
PROMO_ID PROD_ID CUST_FIRST_NAME QUANTITY_SOLD
———- ———- ——————– ————-
33 30 Cole 1
33 21 Nicholas 1
33 46 Nicholas 1
33 128 Nicholas 1
33 30 Joshua 1
33 46 Joshua 1
33 128 Joshua 1
33 147 Joshua 1
33 30 Cole 1
33 46 Cole 1
33 128 Cole 1
33 147 Cole 1
33 21 Cole 1
33 147 Cole 1
The three-table inner join example as run in Toad for Oracle is shown in Figure 15.
Figure 15.
Outer joins over three tables may also be made. An example three-table LEFT OUTER JOINis as follows:
SELECT SH.SALES.PROMO_ID, SH.PRODUCTS.PROD_ID, SH.CUSTOMERS.CUST_FIRST_NAME, SH.SALES.QUANTITY_SOLD
FROM SH.PRODUCTS LEFT JOIN SH.SALES
ON SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID
LEFT JOIN SH.CUSTOMERS
ON SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID
WHERE SH.SALES.PROMO_ID < 35;
PROMO_ID PROD_ID CUST_FIRST_NAME QUANTITY_SOLD
———- ———- ——————– ————-
33 30 Cole 1
33 21 Nicholas 1
33 46 Nicholas 1
33 128 Nicholas 1
33 30 Joshua 1
33 46 Joshua 1
33 128 Joshua 1
The three-table left join example as run in Toad for Oracle is shown in Figure 16.
Figure 16.
Three-table queries may combine the different types of joins. An example query that includes an inner join and a left outer join is as follows:
SELECT SH.SALES.PROMO_ID, SH.PRODUCTS.PROD_ID, SH.CUSTOMERS.CUST_FIRST_NAME, SH.SALES.QUANTITY_SOLD
FROM SH.SALES LEFT JOIN SH.CUSTOMERS
ON SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID INNER JOIN SH.PRODUCTS
ON SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID
WHERE SH.SALES.PROMO_ID < 35;
PROMO_ID PROD_ID CUST_FIRST_NAME QUANTITY_SOLD
———- ———- ——————– ————-
33 30 Cole 1
33 21 Nicholas 1
33 46 Nicholas 1
33 128 Nicholas 1
33 30 Joshua 1
33 46 Joshua 1
33 128 Joshua 1
Some issues about fully qualified table names
Some issues regarding specifying fully qualified table names exist when one tries to query multiple tables. One issue is that the column name should not be ambiguously defined. As an example:
SELECT CUST_ID, PROD_ID, quantity_sold
FROM SH.SALES
INNER JOIN SH.CUSTOMERS
ON SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID
WHERE ROWNUM<=10
ORDER BY SH.CUSTOMERS.CUST_ID;
>> SELECT CUST_ID, PROD_ID, quantity_sold
FROM SH.SALES
INNER JOIN SH.CUSTOMERS
ON SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID
WHERE ROWNUM<=10
ORDER BY SH.CUSTOMERS.CUST_ID
*
Error at line 1
ORA-00918: column ambiguously defined
To fix the error, use the following query instead:
SELECT SH.CUSTOMERS.CUST_ID, PROD_ID, quantity_sold
FROM SH.SALES
INNER JOIN SH.CUSTOMERS
ON SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID
WHERE ROWNUM<=10
ORDER BY SH.CUSTOMERS.CUST_ID;
Another example is that when using the USINGclause, the column name that is specified in the USINGclause should not be qualified in the query. As an example:
SELECT SH.CUSTOMERS.CUST_ID, PROD_ID, quantity_sold
FROM SH.SALES
LEFT OUTER JOIN SH.CUSTOMERS
USING CUST_ID
WHERE ROWNUM<=10
ORDER BY SH.CUSTOMERS.CUST_ID;
ORA-25154: column part of USING clause cannot have qualifier
To fix the error use the following query:
SELECT CUST_ID, PROD_ID, quantity_sold
FROM SH.SALES
LEFT OUTER JOIN SH.CUSTOMERS
USING (CUST_ID)
WHERE ROWNUM<=10
ORDER BY CUST_ID;
In this article we discussed using Toad for Oracle for multi-table queries.
Related Links
Blog: Code analysis: Why PL/SQL code quality matters
Blog: SQL query optimization: Level up your SQL performance tuning
Blog: Supported Oracle 21c new features in Toad for Oracle 15.1
Blog: Best developer tool: An Oracle Ace's favorite things in Toad for Oracle Base
Blog: Dark mode has arrived for Toad for Oracle 15.1
Blog: SQL tools – 8 ways you can’t live (or work) without them
Have questions, comments?
Head over to the Toad for Oracle forum on Toad World®! Chat with Toad developers, and lots of experienced users.
Help your colleagues
If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.
Start the discussion at forums.toadworld.com