How do you SELECT data from multiple tables in SQL?

You can specify result tables which select data from two or more base tables, two or more views, or even other result tables.

One method is the , which derives a result table that includes the specified columns from two or more base tables, views, or other result tables.

Another method is the , which produces a result table that is actually a set derived from two or more result tables. This method, using the UNION operator, eliminates duplicate rows in the result table. To retain duplicate rows in the set, specify UNION ALL.

The following topics are discussed on this page:

The ability to join two or more tables (and/or views) easily is a major advantage that distinguishes relational systems from nonrelational systems. The join capability simplifies the task of retrieving data from different tables to build a single result table holding all the necessary data.

You implement this join by forming a query which retrieves data from more than one table. Your SELECT statement includes columns (qualified by table name) from two or more tables. The FROM clause of your query names the tables used as qualifiers in the select-statement.

All the columns specified in the SELECT form the result table. Thus, if you specify the column CUST_NO from the CUSTOMERS table and the column CUST_NO from the ORDERS table, your result table includes two CUST_NO columns, each qualified by the original table name.

You can reference up to 20 tables in a FROM clause when you are performing a join. For example, if a view is based on five tables, you can name that view in the FROM clause, and up to fifteen other tables.

The following example joins the CUSTOMERS and ORDERS tables. This example is taken from DBCOBSQA, a sample program available on the installation tape.

You can only join tables which have the same security type, that is to say, either the External Security Model or the SQL Security Model.

List the customer number, name and order ID for those customers who
have outstanding orders.

               .
               .
       (COBOL statements)
               .
               .
  1   EXEC SQL
  2      DECLARE CUSTORD CURSOR FOR
  3          SELECT CUSTOMERS.CUST_NO, ORD_ID, NAME
  4               FROM CUSTOMERS, ORDERS
  5               WHERE CUSTOMERS.CUST_NO = ORDERS.CUST_NO
  6               ORDER BY CUSTOMERS.CUST_NO
  7   END-EXEC
               .
               .
       (COBOL statements)
               .
               .

The SELECT statement specifies the columns to be selected from each table. Column names which are the same in each table are qualified by the table name, such as CUSTOMERS.CUST_NO. ORD_ID is not qualified since it exists only in the ORDERS table and NAME is not qualified since it exists only in the CUSTOMERS table.

Both tables are named in the FROM clause, indicating that the result table includes the retrieved data from each table.

The WHERE clause specifies that the value of the CUST_NO column in each table must be equal to be selected for the result table. If a customer does not have an outstanding order, then the CUST_NO value does not appear in the ORDERS, nor in the result of the join. The comparison is possible since the columns have comparable data types. For comparison rules, see Basic Operations (Assignment and Comparison).

The ORDER BY clause specifies that the rows in the result table be in ascending order according to the customer number.

Following is the report produced by running DBCOBSQA.

 -----------------------------------------------  CURRENT ORDERS  ---------------------------------------------PAGE  1
 CUSTOMER NO CUSTOMER NAME
 ---------------------------------------------------------------------------------------------------------------------
  0030         CANNON TOOLS CO
  0230         CHEMICAL MUTUAL
  1210         LINGBERGH INDUSTRIES
  1210         LINGBERGH INDUSTRIES
  1450         UNION TRANSPORTATION
  1630         MARBURY MATERIALS
  1850         TECH CASTLE RESEARCH
  1890         FIRST STREET BANK CORP
  2050         TRANSAMERICAN PUBLISHING
 END OF REPORT

Using the UNION operator derives a result table by combining two other result tables.

The set of rows in the UNION of result tables R1 and R2 is the set of rows in either R1 or R2, with redundant duplicate rows eliminated. Each row of the UNION table is either a row from R1 or a row from R2.

The columns of the result table are not named.

Two rows are duplicates of one another only if each value in the first row is equal to the corresponding value of the second row.

All but one row of each set of duplicates are eliminated by a UNION. The number of rows in the UNION table is the sum of the number of rows in R1 and R2, less the number of duplicates eliminated.

If you specify UNION ALL, duplicate rows are not eliminated.

Result tables R1 and R2 must have the same number of columns.

With the exception of column names, the description of the first column of R1 must be identical to the description of the first column of R2, that is to say, the data type and the length must be the same. The description of the second column of R1 must be identical to the description of the second column of R2, and so on.

The following example (see next page) performs a union on the result tables derived from the CUSTOMERS and ORDERS tables. This example is taken from DBCOBSQF, a sample program available on the installation tape.

 

List all customer numbers for customers who have more than $300,000 in
year-to-date sales.

               .
               .
       (COBOL statements)
               .
               .
  1   EXEC SQL
  2      DECLARE CUSTORD2 CURSOR FOR
  3          SELECT CUST_NO
  4              FROM CUSTOMERS
  5              WHERE YTD_SALES > 300000
  6          UNION
  7          SELECT CUST_NO
  8              FROM ORDERS
  9              ORDER BY 1
 10   END-EXEC
               .
               .
       (COBOL statements)
               .
               .

The first subselect specifies the column to retrieve from the CUSTOMERS table. The search condition in the WHERE clause limits the retrieved rows to those where the value of YTD_SALES is greater than $300,000. You do not have to qualify the CUST_NO column in this SELECT to distinguish it from the CUST_NO column of the ORDERS table because each subselect is evaluated separately. The union is performed after each subselect has been processed.

The UNION operator between the two subselect statements means the final result table contains data that is a set formed from the data retrieved by each subselect.

The second subselect specifies the column to retrieve from the ORDERS table. No search condition limits the number of rows retrieved by this subselect. The ORDER BY clause specifies that the rows in the result table of this union are to be placed in ascending order according to the value in the first column. This column is referenced by a number because columns do not have names in a result table formed by a union operation.

In the previous example, each subselect has one column and the definition of the column in the first subselect is identical to the definition of the column in the second subselect.

The union of the two result tables would not be possible if one subselect had more columns specified than the other, or the column definitions did not match.

The result table formed by the UNION operation is a set of the data retrieved by each subselect. The columns of the result table are not named.

How do I SELECT all data from multiple tables in SQL?

How do you SELECT data from 3 tables in SQL?

How can I get records from two tables in SQL?

How do you get data from multiple tables in SQL without join?