In this post, we’ll discuss subqueries in SQL server. Let’s understand subqueries with an example.
Let’s look at the tables that we’ll be using to understand subqueries. TblProducts Table contains product-related information like the id of the product, which is also going to act as the primary key for this table, name of the product, Quantity and Price of product and TblProductSale Table contain product sales data.
Every time we sell a product, an entry will be made into TblProductSale table. TblProductSale table contains information like ID, which is going to act as the primary key for the table, productid, the Id of the product that we sold, at what unit price did we sale and how much quantity did we sell?
This insert script inserts some sample data into these two tables. so you can copy it for practice subqueries if required.
USE [TestDatabase] GO /****** Object: Table [dbo].[TblProducts] Script Date: 10/22/2020 12:38:00 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TblProducts]( [Id] [int] NOT NULL, [ProductName] [nvarchar](100) NOT NULL, [Quantity] [int] NOT NULL, [Price] [float] NULL, CONSTRAINT [PK_TblProducts] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO /****** Object: Table [dbo].[TblProductSale] Script Date: 10/22/2020 12:38:00 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TblProductSale]( [Id] [int] NOT NULL, [ProductId] [int] NULL, [QuantitySold] [int] NULL, [Datetime] [datetime] NULL, CONSTRAINT [PK_TblProductSale] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (1, N'Books', 10, 100) GO INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (2, N'Mobile Phone', 100, 15000) GO INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (3, N'Watches', 50, 1000) GO INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (4, N'Cameras ', 30, 10000) GO INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (5, N'Computer Accessories.', 40, 2000) GO INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (1, 1, 10, CAST(N'2020-10-16T17:16:57.953' AS DateTime)) GO INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (2, 2, 5, CAST(N'2020-10-16T17:16:57.953' AS DateTime)) GO INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (3, 1, 10, CAST(N'2020-10-16T17:32:44.040' AS DateTime)) GO INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (4, 3, 10, CAST(N'2020-10-16T17:32:44.040' AS DateTime)) GO INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (5, 5, 10, CAST(N'2020-10-16T17:32:44.040' AS DateTime)) GO INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (6, 2, 10, CAST(N'2020-10-16T17:32:44.040' AS DateTime)) GO
So if you look at the data in [TblProducts] table, there are 5 products with Id 1,2,3,4,5, Books, Mobile Phone, Watches, Cameras, and Computer Accessories.
TblProductSale, table content sales data. Pay attention to productID column. Look at this. We have sold the product with Id equal to 1 two times. That is, we sold Books and Mobile Phone 2 times and Watches and Computer Accessories only once. And if you look at Cameras, we haven’t sold it at least once.
Now let’s say your project manager is asking you to write a query that returns the ID , name and Quantity of all the products that we haven’t sold at least once.
Now, how can we tell if we haven’t sold at least once a product?
If you look at the TblProductSale table, if the productId is not present there, then we can say for sure we haven’t sold that product at least once.
so if we want to retrieve the name and quantity of all the products that are not sold at least once, we want to retrieve all the products from the [TblProducts] table where that ID does not exist in the productId column, in [TblProductSale] table. It’s as simple as that.
Select Id, ProductName, Quantity,Price from [dbo].[TblProducts] where Id not in (Select Distinct ProductId from [dbo].[TblProductSale])
Look at that. How easy it was to use subquery here. So the query that is present inside parenthesis is called a subquery.
Subqueries are always enclosed within parentheses and look at the subquery. It returns only one column. and this column is now used in your where clause and we are using it in “not in”.
Subqueries are also called inner queries. And the query that contains the subquery is called an outer query.
Now many times the subqueries can actually be very easily replaced with the Joins. We have discussed Joins in the in join post series. If you are not familiar with SQL joins, I would strongly encourage you to read the below post.
so let’s re-write the query, which gives us exactly the same output using Joins.
We want to use the left join here. when I use left join, I get all the matching rows between both the tables plus the non-matching row, But that’s not what we want.
What we want is only the non-matching rows from the left table. we only want the non-matching rows.
How do we specify that using a where clause in [TblProductSale], where ProductID is NULL.
Select TblProducts.Id, ProductName, Quantity,Price from TblProducts Left Join TblProductSale ON TblProducts.Id = TblProductSale.ProductId where TblProductSale.ProductId IS NULL
so in this example, we have seen how to use a subquery within the where clause, and we have also seen how to replace that subquery using a join.
both of these queries give us exactly the same output. Let’s look at another example of using subqueries.
Write a query to retrieve the name and total quantity sold.
So if you look at these two tables that we have, I want the name of the product and the total quantity of each product that we have sold. So if we look at the quantity sold, how many Books did we sell?10+10=20
Let’s see how to do that. In this example, we have seen how to use this subquery in the where clause. In this example, we’ll see how to use a subquery in the select list.
Select ProductName, (Select SUM(QuantitySold) from TblProductSale where ProductId = tblProducts.Id) as TotalQuantity from tblProducts order by ProductName
Same result using Joins
Select ProductName, SUM(QuantitySold) as TotalQuantity from tblProducts left join TblProductSale on tblProducts.Id = TblProductSale.ProductId group by ProductName
Let me tell you that subqueries can be nested up to 32 levels.
The post How to write subquery in select statement in Sql appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- How to remove a line from `detail`. (Master detail)?
- Connecting to named instance of Microsoft SQL Server
- MSSQL container fails to start when mapping volumes after upgrading to WSL2
- Deploy of a report with SSDT 2016 generates error
- Sql count where condition is not returning correct result
- Understanding SQL Server behaviour with min(datetime)
- IIS Error: Cannot connect to the configuration database
- local database synchronization between sql server database and local sql ce database
- Long running SQL query filling up tempdb
- The permissions granted to user are insufficient for performing this operation while deploying rdl report
- Select record by given date from and date till.
- Combine continuous datetime intervals by type
- can't abbreviate in SQL?
- Sql statement help
- Using of Xpath in SQL Server
- Using Declared Variables in a Subquery
- What is the best way to refresh a rollup table under load?
- How to get total weight of a case size e.g. 12 x 160g equal to 1.92kg
- How can I get a query displayed monthly with a subquery
- Help identifying forum spammers via a SQL query?
- Connection failed to SQL Server Express using user with SQL Server authentication
- How to avoid this subquery and create an indexed view?
- SQL Server - Use substring in where clause
- Append Changed and New Employees to Existing Table
- Using SQL unpivot on two groups
- Pausing an SQL server script?
- I need to improve my T-SQL query so that i can return the sum of columns
- Leaving a Database Connection Open?
- SQL query to avoid duplicates and order by a row
- SQL Server union temp tables
- Employee that has a higher salary than the AVERAGE of his department - optimized
- Removing rows , if they have duplicate
- Linq to SQL SubmitChanges only for specific table
- Table lock in SSIS transaction
- SQL Sub Queries/Self Join
- How to add an unique constraint on more columns in SQL Server 2008 (R2)?
- error while using sails.js sails-mssql adapter
- How to restore a database from a backup in installshield
- Entity Framework with Sql Server Column Level Encryption
- Testing connection to server on Android
- Combine queries to grab foreign key value from different able based on result in mssql
- How to use Json Parser in CLR Procedure?
- get working working days from calendar Table in sql server
- What's a good alternative to firing a stored procedure 368 times to update the database?
- Tool to upgrade SQL Express database after deployment
- How to make an IF Statement in SSIS
- Join differently depending on conditions
- Update SQL Server database schema using MVC .net with versioning
- Sql Server 2008 not reporting compile errors for missing objects
- QODBC3 Unable to bind to variable
- System.OutOfMemoryException error when I open the Sql Server file
- How to see the connection string used to connect to SQL Server
- User-editing a linked SQL Server table filtered by the results from a passthrough query
- Database drop table
- How to inspect SQL Server database during a transaction?
- Move Columns to next if null
- Difference between dates sql- ignore saturday sunday
- SQL Server stored procedure performance issue - WHILE EXISTS
- Query filter with CASE select NULL field [SQL Server]
- SQL removing duplicates in a certain column
- Storing upload file as binary data in SQL Server database
- Create Table - SQL Server 2008
- Identify Which column cause String or binary data would be truncated. : SQL server
- Can not connect to SQL Server Management Studio
- Show the most frequent item in a one to many SQL query
- SQL Server remote connection error
- How to use windows function to write sql where value of current row depends on previos row
- Default SET NOCOUNT ON in SQL Server 2012
- Operation cannot be performed on database because it is involved in a database mirroring session or an availability group (Azure)
- Is there a Max function in SQL Server that takes two values like Math.Max in .NET?
- Moving primary key from int to Guid
- how to optimize this sql server update request
- SQL Server: Group By based on one column and get columns based on other column
- SQL Select or Insert return ID
- How to get Column Names and other information from SqlServer table?
- Select to Return Multiple Rows from Single Row
- VS Unit test on database procedures inMemory that can be integrated with continuous delivery pipeline
- SQL set operation to update table based on values on another table
- Get a bit as a result of comparing two numbers
- Aggregation query over XML document using T-SQL
- SQL Server EXEC with unquoted string parameter is valid?
- Create view in Oracle SQL Developer from Microsoft SQL Server Management Studio
- What is the most appropriate encryption for a small to medium companys' MSSQL database
- Phonebook database design
- Return objects with populated list properties from stored procedure
- search for a specific value from multiple columns in a table
- How get around the arithmetic overflow error converting expression to data type int?
- Update Table with a "Select query" with a where clause
- SQL: how to get all the distinct characters in a column, across all rows
- EntityFramework package version="6.1.3" and Web config version 184.108.40.206?
- How to check if a view exists and create if it does not
- Store value in a variable after using select statement
- Convert string to datetime value in LINQ
- DBCC execution completed. If DBCC printed error messages, contact your system administrator
- Microsoft JDBC and Datetime2 - rounding issue
- question on deploy package to production server
- SQL Server : how to group only part of the syntax
- LINQ-To-Sql Update - Performance Issues
- SQL Server - Adding an XML Index to a calculated column on a View
- How do you concat multiple rows into one column in SQL Server?
- Insert into different tables depending on parameter value
- Consecutive number for specific value in the field
- Sum of cash and cheque from a table
- T-SQL Query doesn't work with DATE
- TSQL replace([my_string],'NULL',NULL) still returns NULL when [my_string] does not contain the string 'NULL'
- Using a variable in LIKE '%' + @variable brings back wrong results
- How to display two tables separately in a single view with a join query in one table?
- ORDER BY, SELECT with UNION sort difficulty
- SqlException: Cannot open database <database> requested by the login. The login failed
- How to get column names from a query in SQL Server
- Finding the id's which include multiple criteria in long format
- Why is a bulk insert to a memory-optimized non-durable table the same speed as a durable table?
- SQL Server 2012: Running multiple UPDATE FROM SELECT on the same table simultaneously
- Deadlock due to hash collision?
- SQL Server COUNT multiple tables returns WRONG RESULT
- Unnecessary conversion to bigint
- Using a function within a Select statement for conversion
- Protractor - Connect to MS SQL management studio- Is it possible?
- SQL database ID incrementation
- Sql Server Master/Detail search query
- java how retrieve data from database
- Spark and MSSQL insert speed
- SQL Server OLAP CUBES (Tutorials)
- Executing ToList multiple times in IQueryable
- Calculate off days between 2 dates in sql
- Value for Non-Nullable System.DateTime
- Different where condition for each column
- How does SQL Server handle Varchar internally?
- Why is this difference in output ?
- Mechanism in T-SQL similar to SAVE EXCEPTIONS in Oracle
- Get Max(Amount) and associated Type
- Timeout running SQL query
- Change report data visibility based on rendering format in Reporting Services
- Changing an identity seed without losing or changing current data
- Comparing AVG() in SQL
- R odbc does not return SCOPE_IDENTITY after insert
- CONCAT in SQL Server 2008 r3
- mssql - Insert multiple rows back into original table from temporary table
- SQL select where a user has two groups at same time
- Select Count(*) vs Select Count(id) vs select count(1). Are these indeed equivalent?
- TSQL RAND random values
- Convert SQL rows into columns based on separate blocks within the results
- How to find all the descendants with SQL?
- SQL CASE between values of the same table
- sending email with verification link while user subscribed to a site
- Cannot access the package / the package does not exist
- Add date to SQL database backup filename
- SQL Group with Order by
- How to GROUP BY DATETIME column in SQL
- Sql server - "Map" function?