Toad World Blog

Using Toad® for Oracle to Query Multiple Tables

May 24, 2022 8:00:00 AM by Deepak Vohra

The SELECT statement 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 FROM clause, either with or without a JOIN clause separating the tables.

Blog-TW-ToadTableQuery-IM-JY-73153

A SELECT statement 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 WHERE condition 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
MediaBanner-ToadWorld-600x100-IM-JY-63709.psd-1-Jan-18-2022-05-54-52-11-PM

 

The square parentheses around the OUTER indicate that it is optional in the JOIN clause.

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.

INNER JOIN-using toad to query multiple tables

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 left outer join - using toad to query multiple tables

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 JOIN clause 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 JOIN is significant, implying that if the tables on the left and right side of the RIGHT OUTER JOIN clause are switched, the result may be different.

figure 3 right outer join - using toad to query multiple tables

Figure 3.

A full outer join could also be made with the FULL OUTER JOIN clause. 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 sh.products table - using Toad to query multiple tables

Figure 4.

The second sample table is SH.CUSTOMERS (Figure 5) for customers data.

Figure 5. SH.CUSTOMERS table – Using Toad to Query Multiple Tables

Figure 5.

The third sample table is SH.SALES (Figure 6) for sales data.

Figure 6. SH.SALES table – Using Toad to Query Multiple Tables

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 SH schema 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.PRODUCTS table 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 SELECT statement 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 WHERE clause or an INNER JOIN to specify a join condition.

Figure 7. Avoid cartesian queries–Using Toad to Query Multiple Tables

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 JOIN clause with the join condition specified in the ON clause. 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 SELECT statement. As an example, include the ORDER BY clause 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. Inner join – Using Toad to Query Multiple Tables

Figure 8.

An inner join may be made without the INNER JOIN clause with the tables to be joined specified in the FROM clause, and the join condition specified in the WHERE clause. The preceding example without the INNER JOIN and ON clauses 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 JOIN clause. An alternative query using the JOIN clause could be used to generate a query that is equivalent to a LEFT OUTER JOIN query but would be too verbose. An example is an INNER JOIN combined in a UNION with a query on the left table only, with a NOT EXISTS subquery. 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 JOIN clause. An alternative query using the JOIN clause could be used to generate a query that is equivalent to a RIGHT OUTER JOIN query 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. Right outer join – Using Toad to Query Multiple Tables

Figure 9.

Using a FULL OUTER JOIN

A full outer join can only be made with the FULL OUTER JOIN clause. An alternative query using the JOIN clause may exist, and could be used to generate a query that is equivalent to a FULL OUTER JOIN query, 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 USING clause to specify the join condition for an equijoin when using an explicit JOIN clause. It is an alternative to using the more verbose ON clause, 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 USING clause. 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 USING clause example as run in Toad for Oracle is shown in Figure 10.

Figure 10. USING Clause – Using Toad to Query Multiple Tables

Figure 10.

 

Using the PARTITION BY clause

The PARTITION BY clause may be used in a JOIN to 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 BY example as run in Toad for Oracle is shown in Figure 11.

Figure 11. PARTITION BY – Using Toad to Query Multiple Tables

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 IN or NOT EXISTS clause. 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. Antijoin example – Using Toad to Query Multiple Tables

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 EXISTS clause. 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. Semijoin example – Using Toad to Query Multiple Tables

Figure 13.

 

Try Toad free for 30 days. 

Free 30-day Trial

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 JOIN clause 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. Three table join – Using Toad to Query Multiple Tables

Figure 14.

The same three-table join could be made using the INNER JOIN and ON clauses 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. Three table inner join – Using Toad to Query Multiple Tables

Figure 15.

Outer joins over three tables may also be made. An example three-table 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.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 left join– Using Toad to Query Multiple Tables

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 USING clause, the column name that is specified in the USING clause 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.

Tags: Toad for Oracle

Deepak Vohra

Written by Deepak Vohra

Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.