---
title: "How to Write Complex SQL Sub-query"
description: "Here is a sample sub-query. Here you will read records from two tables. Also explained how the query works."
url: https://srinimf.com/2021/11/29/how-to-write-complex-sql-sub-query/
date: 2021-11-29
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2021/11/complex-sub-query-new.png?fit=1200%2C675&ssl=1
categories: ["SQL"]
tags: ["Complex sub-query"]
type: post
lang: en
---

# How to Write Complex SQL Sub-query

Here is a complex sub-query on [how to write](https://srinimf.com/2020/02/02/how-to-to-write-sub-query-quickly/) 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.

Related[Two Sample Queries for Inserting Multiple Rows in SQL](https://srinimf.com/2022/07/25/top-sql-queries-to-insert-single-row-and-multi-rows/)

*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_number | Customer_name |
| --- | --- |
| 123 | Rao |
| 567 | Kyte |
| 897 | Robo |
| 901 | Tup |
| 991 | Vek |
| | |

Advertisements

### Order_table

| Order_number | Customer_number | Order_total |
| --- | --- | --- |
| 1001 | 123 | 100.76 |
| 1002 | 567 | 200 |
| 1003 | 897 | 310 |
| 1004 | 901 | 450 |
| 1005 | 991 | 566 |

### 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](https://srinimf.com/2020/07/05/how-to-use-where-delete-in-sub-queries/)-query, which ultimately reduced complexity, and we got the desired result.

Related posts

- [Two Sample Queries for Inserting Multiple Rows in SQL](https://srinimf.com/2022/07/25/top-sql-queries-to-insert-single-row-and-multi-rows/)DateJuly 25, 2022
- [DB2 SQL: Error Code +100 and Resolution](https://srinimf.com/2018/09/21/sql-query-complex-where-condition-you-need-to-modify-a-row/)DateSeptember 21, 2018
- [2 Top SQL Queries to Update Table: Subquery and Join](https://srinimf.com/2023/11/09/how-to-update-a-table-neat-way-using-subquery/)DateNovember 9, 2023

Get new content delivered directly [to your](https://srinimf.com/2014/03/25/db2-v10-daily-tips-email/) inbox.

Type your email…

Sign up

Related[INSERT and SELECT How to Use in Single Query](https://srinimf.com/2018/04/04/sql-query-insert-values/)

[![30 SQL Practice Queries with Solutions — Run Them Live](https://i0.wp.com/srinimf.com/wp-content/uploads/2026/10/pexels-photo-5530473.jpeg?fit=867%2C1300&ssl=1)](https://srinimf.com/2026/10/01/30-sql-practice-queries-with-solutions-run-them-live/)

## [30 SQL Practice Queries with Solutions — Run Them Live](https://srinimf.com/2026/10/01/30-sql-practice-queries-with-solutions-run-them-live/)

Practice 30 SQL queries with solutions, from SELECT and JOINs to subqueries and window functions. Run SQL queries live in an interactive SQL Playground.

[![Supercharge Your Database Skills: Try Our New Interactive SQL Plugin!](https://i0.wp.com/srinimf.com/wp-content/uploads/2026/09/pexels-photo-1181469.jpeg?fit=1200%2C801&ssl=1)](https://srinimf.com/2026/09/30/supercharge-your-database-skills-try-our-new-interactive-sql-plugin/)

## [Supercharge Your Database Skills: Try Our New Interactive SQL Plugin!](https://srinimf.com/2026/09/30/supercharge-your-database-skills-try-our-new-interactive-sql-plugin/)

Struggling with database queries? Explore our new interactive SQL plugin to write, test, and master SQL directly in your browser. Try the live demo now!

[![How to Count Spark Stages by Sight and Optimize Shuffles](https://i0.wp.com/srinimf.com/wp-content/uploads/2026/09/pexels-photo-14314638.jpeg?fit=1200%2C800&ssl=1)](https://srinimf.com/2026/09/27/how-to-count-spark-stages-by-sight-and-optimize-shuffles/)

## [How to Count Spark Stages by Sight and Optimize Shuffles](https://srinimf.com/2026/09/27/how-to-count-spark-stages-by-sight-and-optimize-shuffles/)

Want to predict execution stages before running a job? Learn how to count Spark stages by sight with code breakdowns, shuffle rules, and optimization traps.

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2021%2F11%2F29%2Fhow-to-write-complex-sql-sub-query%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2021%2F11%2Fcomplex-sub-query-new.png%3Ffit%3D1200%252C675%26ssl%3D1&description=How%20to%20Write%20Complex%20SQL%20Sub-query)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2021/11/29/how-to-write-complex-sql-sub-query/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2021/11/29/how-to-write-complex-sql-sub-query/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2021/11/29/how-to-write-complex-sql-sub-query/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2021/11/29/how-to-write-complex-sql-sub-query/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20How%20to%20Write%20Complex%20SQL%20Sub-query&body=https%3A%2F%2Fsrinimf.com%2F2021%2F11%2F29%2Fhow-to-write-complex-sql-sub-query%2F&share=email)
-

### Like this:

Like Loading…