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.
716 questions
54
votes
8
answers
230k
views
Get multiple columns from a select subquery
SELECT
*,
p.name AS name,
p.image,
p.price,
(
SELECT ps.price
FROM product_special ps
WHERE p.id = ps.id
AND ps.date < NOW()
ORDER BY ps....
- 633
49
votes
4
answers
69k
views
Using column alias in a WHERE clause doesn't work
Given a table users with two fields: id and email.
select id, email as electronic_mail
from (
select id, email
from users
) t
where electronic_mail = ''
Postgres complains that:
...
- 627
35
votes
3
answers
6k
views
Why does this query work?
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);
From ...
- 523
33
votes
1
answer
178k
views
Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used
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.
SELECT ...
- 433
31
votes
2
answers
49k
views
Subqueries' aliases same as main queries' aliases
I have an SQL query whose aliases are the same as some of its subquery's aliases.
For example:
select *
from ROOM r
where ...
(
select *
from ROAD r
...
- 517
26
votes
2
answers
140k
views
SELECTing multiple columns through a subquery
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.
SELECT
DISTINCT petid,
userid,
(SELECT ...
- 393
16
votes
3
answers
2k
views
Slow update on large table with subquery
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.
UPDATE [SourceTable]
SET
Bad_Count=
...
- 161
16
votes
1
answer
2k
views
Why does subquery use parallelism and join doesn't?
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.
Join ...
- 285
15
votes
3
answers
24k
views
PostgreSQL query very slow when subquery added
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
LIMIT 5000;
EXPLAIN ANALYZE output:
Limit (cost=8.84..12.86 ...
- 901
15
votes
2
answers
5k
views
How can SQL Delete using a sub query
The following code was added by one of our developers to delete duplicate records from the table:
DELETE SubQuery
FROM
(
SELECT ID
,FK1
,FK2
,...
- 3,272
15
votes
1
answer
474
views
Poor performing subquery with date comparisons
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 ...
- 255
13
votes
4
answers
21k
views
MySQL subquery slows down drastically, but they work fine independently
Query 1:
select distinct email from mybigtable where account_id=345
takes 0.1s
Query 2:
Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
- 395
13
votes
1
answer
29k
views
Call function where argument is a (sub)select statement
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 ...
- 245
11
votes
3
answers
4k
views
SQLITE: A problem of tags and products
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
Let's ...
- 213
11
votes
2
answers
4k
views
Subquery gives no error for a non-existing column with the same name as in the outer query
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 ...
- 197
11
votes
2
answers
15k
views
How to combine ORDER BY and LIMIT with an aggregate function?
Here is a fiddle for my question.
I have a simple table layout:
class
person: belongs to a class
I want to select all classes, and for each class, I want the first two person identifiers of the ...
- 455
11
votes
1
answer
28k
views
MySQL: Optimize UNION with "ORDER BY" in inner queries
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 ...
- 213
10
votes
1
answer
413
views
Why is my SQL Server query behaving differently on UPDATE than on SELECT?
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 = ...
9
votes
2
answers
5k
views
Why wouldnt Mysql use index on the Primary query in a nested query
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 ...
- 91
8
votes
3
answers
16k
views
No advantage of using Cross Apply or CTE over inline sub-query
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
;WITH cte_count
AS
(
SELECT COUNT(1) c, OrderId FROM ...
- 287
8
votes
3
answers
13k
views
Performance of count(*) in subquery
Suppose we have the following queries:
1.
SELECT COUNT(*) FROM some_big_table WHERE some_col = 'some_val'
2.
SELECT COUNT(*) FROM ( SELECT * FROM some_big_table WHERE some_col = 'some_val' ...
- 195
8
votes
3
answers
44k
views
Check if any of the values is in a subquery result
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 ...
- 83
8
votes
1
answer
6k
views
Why is SQL Server spliting it's (JSON) response into multiple rows?
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 ...
- 311
8
votes
2
answers
12k
views
JSON subquery using WITHOUT_ARRAY_WRAPPER returning odd data?
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 ...
- 4,043
8
votes
1
answer
4k
views
Replace long GROUP BY list with a subquery
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 ...
- 193
8
votes
1
answer
10k
views
Should i use subquery to limit table before a join?
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:
SELECT *
FROM Customers
NATURAL JOIN Orders
WHERE ...
- 415
8
votes
1
answer
38k
views
Passing a value to a nested subquery
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, [ ... ],
(SELECT
COALESCE(craft_w8_a.weight, 0) +...
- 183
8
votes
1
answer
13k
views
Query performance with subquery and IN clause
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:
...
- 183
8
votes
1
answer
11k
views
Why does SQL Server run a subquery for each row of the table it's qualifying?
This query runs in ~21 seconds (execution plan):
select
a.month
, count(*)
from SubqueryTest a
where a.year = (select max(b.year) from SubqueryTest b)
group by a.month
When the subquery ...
- 464
8
votes
1
answer
24k
views
Subqueries run very fast individually, but when joined are very slow
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....
- 1,240
8
votes
2
answers
6k
views
Optimize select on subquery with COALESCE(…)
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:
SELECT ...
- 183
8
votes
1
answer
1k
views
How can I get the hierarchical values from the below query?
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 ...
- 471
8
votes
2
answers
124
views
Changes access method for non-correlated subquery
Oracle 11g R2
Unfortunately our application has per row security "features".
We have a query that looks about like this:
Bad, slow:
SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
...
- 1,569
7
votes
2
answers
11k
views
best way to avoid redundant aggregate functions and/or group by columns
Suppose I have two tables:
Foo:
id
baz
Bar:
id
foo_id
boom
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 ...
- 183
7
votes
2
answers
356
views
Correlated Subquery SQL Server 2014
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 ...
- 875
7
votes
2
answers
10k
views
Does Postgres optimize this JOIN with subquery?
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:
SELECT po.id,
SUM(...
- 73
7
votes
4
answers
85k
views
MYSQL Subquery in columns of select statement
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
FROM ...
- 415
7
votes
2
answers
745
views
PostgreSQL randomising combinations with LATERAL
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 (
line INT
);
INSERT INTO foo (line)
SELECT generate_series(0, 999, 1);
Let'...
- 1,730
7
votes
1
answer
9k
views
Subquery can't find column from superquery's join
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" ...
- 294
6
votes
3
answers
384
views
Which is the most efficient way to run a particular select query?
If I run the following code:
select PolicyNumber, MAX(decpageid) as decpageid, Risk
from StatRiskDecpages
where PolicyNumber = 'AR-0000301132-04'
group by PolicyNumber, Risk
I get the following ...
- 3,283
6
votes
1
answer
780
views
Unused columns in WHERE clause
I found this query in our codebase:
DELETE FROM "Foo"
WHERE ("Foo"."Id", "Foo"."CreatedAt")
IN (SELECT "f"."Id", "f".&...
- 205
6
votes
2
answers
2k
views
Is there a way to hint to query optimizer to MySQL which constraints should be done first?
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
(
SELECT *
FROM
...
- 1,355
6
votes
3
answers
26k
views
MySQL: Delete all but last N records
Consider the following table:
mysql> DESCRIBE pixels;
+---------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default ...
- 11.9k
6
votes
3
answers
8k
views
Return 2 columns from MySQL subquery?
I'm new in MySQL programing and I have one problem.
I created this query:
SELECT *,
topics.createdate AS TopicCreateDate,
(SELECT (SELECT username
FROM users
...
- 219
6
votes
2
answers
35k
views
How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values?
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 ...
- 263
6
votes
2
answers
124
views
Retrieving data of vehicle drivers that have not been assigned to a job
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 ...
- 95
6
votes
1
answer
8k
views
mySQL query optimisation — multiple joins or select … where not in (select distinct…)?
Background
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 ...
- 165
6
votes
1
answer
433
views
Why an incorrect JOIN using correlated sub-query is so much slower
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 ...
- 163
5
votes
3
answers
6k
views
Query to find GUID's "not in" null containing subquery returns no results
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 ...
- 203
5
votes
1
answer
581
views
How to get all records from one table and see if they are used in another without a sub query
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 ...
- 191