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
| CUstomer_number | Customer_name |
|---|---|
| 123 | Rao |
| 567 | Kyte |
| 897 | Robo |
| 901 | Tup |
| 991 | Vek |
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-query, which ultimately reduced complexity, and we got the desired result.
Related posts
Get new content delivered directly to your inbox.
Secure S3 File Upload Using API Gateway, Lambda & PostgreSQL (Complete AWS Architecture Guide
Modern applications often allow users to upload files—documents, invoices, images, or datasets. But a production-grade upload pipeline must be secure, scalable, and well-organized. In this article, we will build a complete end-to-end architecture where: We will implement this using Amazon API Gateway, AWS Lambda, PostgreSQL, and Amazon S3. This architecture is widely used in cloud-native…
AI Agents in Data Engineering: Everything You Need to Know
AI agents are revolutionizing data engineering by automating tasks such as monitoring pipelines, generating SQL queries, and ensuring data quality. They enhance productivity, speed up troubleshooting, and improve data accessibility for users. While offering significant advantages, AI agents also face challenges in security, accuracy, and integration with existing systems.
The End-to-End AI Stack – A Real Guide for Developers to Code, Create, and Execute
Artificial Intelligence tools are on the rise, from writing assistants to coding helpers and automation platforms. However, many professionals struggle to compare these tools effectively. This is where the AI Stack becomes important. Modern AI tools like ChatGPT, NotebookLM, and Antigravity serve different purposes, and understanding their roles helps in: Layer 1: Conversational AI (Thinking…
Srinimf 