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

Why DELETE with Subqueries Fails in PySpark SQL (And How to Fix It)

Learn why PySpark SQL DELETE with WHERE IN subquery fails and how to fix it using DELETE USING, Delta tables, and join-based deletes.

GitHub Features & Settings Explained: The Ultimate GitHub Options Guide

GitHub options explained in detail. Explore GitHub features, settings, and best practices to manage repositories and workflows effectively.

Ingesting Data from AWS S3 into Databricks with Auto Loader: Building a Medallion Architecture

In this blog post, we will explore efficient methods for ingesting data from Amazon S3 into Databricks using Auto Loader. Additionally, we will discuss how to perform data transformations and implement a Medallion architecture to improve the management and processing of large datasets. What is the Medallion Architecture? The Medallion architecture is a data modeling…