SQL Subqueries
SQL Subqueries provide a powerful means to combine data from one or more table into one result. They are sometimes called nested queries. As the name implies, they one or more queries, one inside one another other.
IF you’re unfamiliar them, then your first order of business should be to read our article Ultimate Guide to Subqueries. This is super place to start.
Subqueries are slippery and our guide help you get a handle on all the places to use a sub query in your SQL. If you are just looking to learn sub queries, then this is a must read.
If you looking for advanced topics then check out Correlated Subqueries in SQL to see how to write SQL that is more data driven, and doesn’t need to rely on hard coded values.
Or check out our case study on using a Join, Union, and Subquery all in one SQL Statement! This is based on question I received from a reader. It a good read, and give you good insight into my approach to solving problems as well as writing SQL.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. You can get started using these free tools using my guide Getting Started Using SQL Server
Latest Posts
-
Is it possible to use Joins, Subqueries, and Unions on Multiple Tables?
A reader recently asked me to help them solve a problem combining data from multiple tables into a single result. What was interesting was that the solution used the three ways to combine data that we have recently covered, namely: joins, set operators, and subqueries. Given the comprehensive nature of this problem I thought it…
-
How do I combine results from several SQL tables (hint: there are three ways)
Many times in a relational database the information you want to show in your query is in more than one table. This begs the question “How do you combine results from more than one table?” All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can…
-
SQL ANY and ALL Operators
SQL ANY and ALL keywords are used in subquery comparisons to compare a set of value against all values in the result or any one value in the set. Comparison operators such as greater than, less than, equal, and not equal can be modified in interesting ways to enhance comparisons done in conjunction with WHERE…
-
SQL IN Operator
The SQL In operator compare a column to a list. In this article we’ll see how to use In and NOT IN with list generated using subqueries. SQL IN Operator and NOT IN Operator Review The SQL IN operator is considered a membership type. The membership type allows you to conduct multiple match tests compactly…
-
Derived Tables
A derived table is a subquery in the from clause. It returns a table as its result. Unlike other subqueries you name them. In this articles I’ll show you to use derived tables, a how to use their special abilities to work around SQL limitations. Things you can do with Derived Table Subqueries. There are…
-
Correlated Subqueries in SQL
Correlated subqueries are used to tie an inner query’s criteria to values within the outer query. They are powerful technique to avoid “hard coding” values. In this article look at a couple example and compare a correlated sub query to a join. All the examples for this lesson are based on Microsoft SQL Server Management…
-
SQL Bill of Materials Example
In this puzzle we’ll explore a SQL bill of materials example. Companies use a BOM (Bill of Materials) to itemize the components and sub assemblies used to construct their products. Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned. Once you have figured out the puzzle, post you answer…
-
Subqueries versus Joins
Use both Joins and subqueries to query data from different tables. Though they may even share the same query plan, are many differences between them. Knowing the differences and when to use either a join or subquery to search data from one or more tables is key to mastering SQL. All the examples for this…
-
SQL Subqueries – The Ultimate Guide
SQL subqueries make it possible for you to write queries that are more dynamic, and data driven. Think of them as query within another query. In this article I’ll introduce you to subqueries and some of their high-level concepts. I will show you how to write a SQL subquery as part of the SELECT statement. Subqueries return two types of results: …
-
How to use the IN Operator with a SubQuery
In this video we’ll walk you though how to use the IN operator with a Subquery; we’ll show you why you would want to use IN with a subquery. In it’s simplest form the IN statement matches a column values to a list. TRUE is returned if there is a match. A main advantage of…
-
Joins vs Subqueries SQL Puzzle
In this puzzle, we’re going to learn about joins vs subqueries. In many queries you can substitute joins and subqueries. Yet, since each has their strengths, it isn’t wise to do so. Once you have worked through this puzzle you see some of the benefits and disadvantages of joins vs subqueries. Solving puzzles is a…
-
Rewrite Subquery as a Join
In this puzzle, we’re going to learn how to rewrite a subquery using inner joins. Knowing about a subquery versus inner join can help you with interview questions and performance issues. Though subqueries have unique abilities, there are times when it is better to use other SQL constructs such as joins. By reading this article…
-
Using WHERE EXISTS in SQL
The SQL EXISTS operator is mostly used to test whether a subquery returns rows. It returns TRUE if rows exists in the subquery and FALSE if they do not. SQL Exists The EXISTS condition is used in combination with a subquery. It returns TRUE whenever the subquery returns one or more values. In its simplest…
