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.

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

Order_table

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

Quick SQL Interview Questions for Data Engineers (Little Tricky)

Prepare for Data Engineer interviews with quick SQL interview questions, tricky SQL queries, table creation scripts, joins, window functions, ranking, duplicate records, latest row logic, and real-world examples

AWS Glue Crawler Issue with Dynamic S3 Folder Paths? Here’s the Complete Fix

Learn how to fix AWS Glue crawler issues when S3 paths contain dynamically changing folders like hash values or UUIDs. Step-by-step beginner-friendly guide.

How to Create a Generic Stored Procedure for KPI Calculation (SQL + AWS Lambda)

In modern data engineering, building scalable and reusable systems is essential. Writing separate SQL queries for every KPI quickly becomes messy and hard to maintain. A better approach?👉 Use a Generic Stored Procedure powered by Dynamic SQL, and trigger it using AWS Lambda. In this blog, you’ll learn: What is a Generic Stored Procedure? A…