How to query several tables with the same structure in Oracle database

To query multiple tables with the same structure, you can use the UNION operator to connect the SELECT statements of each table to ensure that the number and type of columns match. Specific steps include: 1. Determine the columns to be queried; 2. Write a UNION query; 3. Execute the query; 4. Optional: Use DISTINCT to eliminate duplicates.

Query multiple tables with the same structure in Oracle database

To query multiple tables with the same structure, you You can use the UNION operator. The UNION operator combines rows from different tables into a single result set.

Syntax:

<code class="sql">SELECT column_list
FROM table1
UNION
SELECT column_list
FROM table2
UNION
...
SELECT column_list
FROM tableN;</code>

Steps:

1. Determine the column to be queried: Determine the columns to extract from each table. The columns should be of the same data type and order.
2. Write the query: Use the UNION operator to join a SELECT statement for each table. Make sure that the number of columns after the UNION matches the number of columns returned in each SELECT statement.
3. Execute query: Run the query to get the combined results from all tables. The result set will contain duplicate records, but you can use the DISTINCT keyword to eliminate duplicates.

Example:

Suppose you have three tables with the same structure: employees, customers and orders. To query all records in these three tables, you can use the following query:

<code class="sql">SELECT *
FROM employees
UNION
SELECT *
FROM customers
UNION
SELECT *
FROM orders;</code>

This query will return all records in all three tables, including duplicate records. To eliminate duplicates you can use the following query:

<code class="sql">SELECT DISTINCT *
FROM employees
UNION
SELECT DISTINCT *
FROM customers
UNION
SELECT DISTINCT *
FROM orders;</code>

The above is the detailed content of How to query several tables with the same structure in Oracle database. For more information, please follow other related articles on the PHP Chinese website!