Stack Exchange Network
Stack Exchange network consists of 181 Q&A communities including
Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
Visit Stack Exchange
Questions tagged [subquery]
SQL term used to describe when a `SELECT` statement is used as part of a larger SQL statement. The larger statement may be DML and is always found within brackets or parenthesis.
p.name AS name,
FROM product_special ps
WHERE p.id = ps.id
AND ps.date < NOW()
ORDER BY ps....
Apr 2, 2012 at 23:18
Given a table users with two fields: id and email.
select id, email as electronic_mail
select id, email
where electronic_mail = ''
Postgres complains that:
Dec 27, 2018 at 18:24
I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c.
Why does this query not return an exception?
select * from table_a where name in (select name from table_b);
Jun 27, 2016 at 8:27
I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine.
Jan 10, 2015 at 7:02
I have an SQL query whose aliases are the same as some of its subquery's aliases.
from ROOM r
from ROAD r
Apr 14, 2012 at 15:06
I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them.
Feb 13, 2013 at 12:58
With SourceTable having >15MM records and Bad_Phrase having >3K records, the following query takes almost 10 hours to run on SQL Server 2005 SP4.
Sep 23, 2015 at 23:11
Why does SQL server use parallelism when running this query which uses a subquery but it doesn't when using a join? The join version runs in serial and takes around 30 times longer to complete.
Jan 22, 2014 at 15:05
The following code was added by one of our developers to delete duplicate records from the table:
Apr 1, 2016 at 5:22
When using a subquery to find the total count of all prior records with a matching field, the performance is terrible on a table with as little as 50k records. Without the subquery, the query executes ...
May 14, 2017 at 16:22
I have a relatively simple query on a table with 1.5M rows:
SELECT mtid FROM publication
WHERE mtid IN (9762715) OR last_modifier=21321
EXPLAIN ANALYZE output:
Limit (cost=8.84..12.86 ...
Sep 7, 2015 at 9:05
I'm searching for a way to create a query to do the following:
Let's consider 3 tables:
products: List of products
tags: list of tags
tag_ties: table used to associate a tag to a product
Nov 4, 2011 at 22:15
select distinct email from mybigtable where account_id=345
Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
Mar 7, 2012 at 16:36
My function takes an int4 as argument and returns a table:
SELECT * FROM test_function(545421); -- works fine
SELECT * FROM test_function(SELECT customerid
FROM tableX ...
Apr 15, 2015 at 12:54
I just set up a logging system which consists of multiple tables with the same layout.
There is one table for each data source.
For the log viewer, I want to
UNION all the log tables,
filter them ...
Sep 24, 2012 at 2:04
A fiddle for my question can be found on https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3cd9335fa07565960c1837aa65143685.
I have a simple table layout:
person: belongs to a class
I want to ...
Jul 30, 2018 at 17:03
I've written a SQL Server query that updates records to have a sequential number after partitioning on a field. When I run it as a SELECT statement, everything looks great:
DECLARE @RunDetailID INT = ...
Mar 10, 2015 at 1:20
I have two tables: search_criteria and pricing.
There is an index on search_id column in search_criteria table and on pricing_id column in pricing table.
But running this nested query does not use ...
Dec 2, 2014 at 9:34
I came across a query like this:
SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C]
FROM Items i
I changed it to following
SELECT COUNT(1) c, OrderId FROM ...
Jul 10, 2015 at 3:10
I have two tables in a MySQL database - t1 with a column c1, and t2 with a column c2.
I run this query:
select * from t1 where c1 in (select c1 from t2);
The above query should give an error as c1 ...
Sep 29, 2018 at 8:51
I have a complicated subquery that returns a list of order IDs. I need to get a list of customers who have these orders. The problem is that there are two ways of assigning a customer to an order (one ...
Apr 21, 2015 at 11:53
This is a repost of my question on Stack Overflow. They suggested to ask it here:
I found an online article from 2005, where the author claims, that many devs use GROUP BY wrong, and that you should ...
Oct 14, 2019 at 6:43
I have the following query (stripped down for brevity), the aim of which is to create the w8 value used to order the results:
SELECT elements.id, [ ... ],
COALESCE(craft_w8_a.weight, 0) +...
Feb 9, 2017 at 18:12
In the case of a join followed by a where clause, would it be better to use a subquery to limit the results, and then do the join? Example:
NATURAL JOIN Orders
Apr 1, 2017 at 22:02
This query runs in ~21 seconds (execution plan):
from SubqueryTest a
where a.year = (select max(b.year) from SubqueryTest b)
group by a.month
When the subquery ...
Jan 22, 2014 at 23:13
I have a large view that I use from within an application. I think I've narrowed down my performance problem, but I'm unsure how to fix it. A simplified version of the view looks like this:
Jul 14, 2014 at 9:54
I have a table named Category which has a column named CategoryID. There is a referencing column in the same table called fParentCategoryID.
I need to get all the category IDs and their subcategory ...
Sep 9, 2015 at 23:32
Suppose I have two tables:
So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set of ...
May 17, 2016 at 1:39
I am trying to build a query that results in a JSON object generated by SQL Server. I've found that I can use subqueries to populate a field (in this case the questions field) with a JSON string that ...
Nov 13, 2020 at 0:13
I am (unsuccessfully) using a correlated subquery on an Invoices table:
Invoices(InvoiceID, VendorID, InvoiceTotal, PaymentTotal, CreditTotal,.... ),
to find the sum of largest unpaid invoices by ...
Mar 1, 2016 at 6:23
In the following example I have a table foo from which I'd like to pick out at random a row per group.
CREATE TABLE foo (
INSERT INTO foo (line)
SELECT generate_series(0, 999, 1);
May 15, 2017 at 6:42
How are subqueries in the column field of select (projection) paired with the result of the main query? in the form:
SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id) as name
Mar 30, 2017 at 17:37
SQL Server 2016, I'm attempting to work with some regular data and return a JSON object for processing by another system. The other system does not recognize the array wrapper, and so I am attempting ...
Aug 30, 2016 at 23:07
I am trying to select a range of data for multiple devices (unique serial numbers) from a historical table and was wondering why there is such a big difference in time for the following queries:
Jan 11, 2012 at 23:21
ypercube solved the problem. Subqueries were totally unnecessary, and the whole thing works with plain joins. It is still strange that MySQL's optimizer could not make use of my original query, though....
Jan 12, 2013 at 7:12
I'm running into some trouble with SQL: Basically I'm trying to get a result set back that contains a sum of ALL questions asked to employees (grouped by company) and also add the "onetime_items" ...
Jan 15, 2016 at 8:40
Oracle 11g R2
Unfortunately our application has per row security "features".
We have a query that looks about like this:
SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
Mar 11, 2014 at 19:28
Suppose we have the following queries:
SELECT COUNT(*) FROM some_big_table WHERE some_col = 'some_val'
SELECT COUNT(*) FROM ( SELECT * FROM some_big_table WHERE some_col = 'some_val' ...
Mar 23, 2017 at 18:40
If I run the following code:
select PolicyNumber, MAX(decpageid) as decpageid, Risk
where PolicyNumber = 'AR-0000301132-04'
group by PolicyNumber, Risk
I get the following ...
Mar 14, 2012 at 14:36
I found this query in our codebase:
DELETE FROM "Foo"
WHERE ("Foo"."Id", "Foo"."CreatedAt")
IN (SELECT "f"."Id", "f".&...
Apr 6, 2022 at 15:48
This is my current query:
SELECT BusinessID as ID,
111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from
Jul 27, 2012 at 10:55
I'm new in MySQL programing and I have one problem.
I created this query:
topics.createdate AS TopicCreateDate,
(SELECT (SELECT username
Nov 22, 2015 at 10:10
In Postgres 12, I have a table purchase_orders and one for its items. I'm running a query that returns PO's for a given shop and a sum of items ordered on each PO:
Jun 27, 2020 at 22:44
I'm running MySQL 5.0.88 (Coldfusion8)
I have a product search which I'm querying number-of-results as well as min/max prices/rebates across the product table. I also want to include a string of ...
Aug 30, 2012 at 9:02
Struggling with something for the past 24 hours. Needed some genius to shine some light please. I am trying to display a weekly job rota/log for drivers, which will display:
Jobs that have not been ...
Sep 24, 2016 at 14:44
I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
Mar 29, 2012 at 13:46
I'm doing some fairly lightweight data massaging/cleaning and ran into a problem where one version of JOIN using a correlated sub-query (probably an erroneous one) ran much much slower than what I ...
Mar 4, 2019 at 16:22
When I have a query that checks if a column of type uniqueidentifer does not exist in a table that has a null value then I get no results back. If the subquery does not return a null it works fine and ...
Aug 24, 2013 at 0:33
I'm trying to write a SQL statement that pulls down all the records from one table and looks to see if they are used in another. SQL is not my strongest language, so I'm sure this is a pretty simple ...
Sep 24, 2017 at 22:29
SELECT users.*,(SELECT COUNT(user_id) AS mutual_connection FROM
SELECT sender_id AS user_id
Feb 21, 2018 at 11:33
By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our
Accept all cookies
Necessary cookies only