I want to perform a query on a result of another query in Oracle Database. In this case, I want to get all the customers information from customer table customer(customer_name(PK), customer_street, customer_city) , Who are included after the following query:

SELECT customer_name
FROM depositor
UNION
SELECT customer_name
FROM borrower

All the tables:

1. Branch (branch_name, branch_city, assets)
2. Customer (customer_name, customer_street, customer_city)
3. Account (account_number, branch_name, balance)
4. Loan (loan_number, branch_name, amount)
5. Depositor (customer_name, account_number)
6. Borrower (customer_name, loan_number)

CodePudding user response：

You can use EXISTS:

SELECT *
FROM   customer c
WHERE  EXISTS (SELECT 1
               FROM   depositor d
               WHERE  c.customer_name = d.customer_name)
OR     EXISTS (SELECT 1
               FROM   borrower b
               WHERE  c.customer_name = b.customer_name)

or:

SELECT *
FROM   customer c
WHERE  EXISTS (
          SELECT 1
          FROM   depositor d
          WHERE  c.customer_name = d.customer_name
          UNION ALL
          SELECT 1
          FROM   borrower b
          WHERE  c.customer_name = b.customer_name
       )

or IN:

SELECT *
FROM   customer
WHERE  customer_name IN ( SELECT customer_name FROM depositor )
OR     customer_name IN ( SELECT customer_name FROM borrower )

or:

SELECT *
FROM   customer
WHERE  customer_name IN (
         SELECT customer_name FROM depositor
       UNION ALL
         SELECT customer_name FROM borrower
       )