How to Write Complex SQL Sub-query

Here is a complex sub-query on how to write it using two tables explained. One is the customer table, and the other one is the order table.

Sub-queries are two types. Single row return and Multiple-row return. Below is the multiple-row return example that explains in detail.

Complex sub query

Sub-query Question

A subquery should get everything from the Customer_Table if the customer has an order in the Order_Table of greater than $400.

Customer_Table

CUstomer_numberCustomer_name
123Rao
567Kyte
897Robo
901Tup
991Vek
Advertisements

Order_table

Order_numberCustomer_numberOrder_total
1001123100.76
1002567200
1003897310
1004901450
1005991566

Here is Sub-query

select *
from customer_table
where customer_number in
(select customer_number
from order_table
where order_total > 400);

How it works

The sub-query is inside the parenthesis. First, it gets the customer_number from the order_table (the order_total greater than 400).

The outer query gets all the details of customer_table for all the matching customer numbers.

Summary

I chose to use sub-query, which ultimately reduced complexity, and we got the desired result.

Related posts

Get new content delivered directly to your inbox.

2 Amazing Ways to Limit Rows in Oracle SQL Query

These two ways limit the number of rows return from an SQL query. These are helpful for Oracle and other SQL developers.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.