Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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.

123 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8 votes
2 answers
122 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 ( ...
rfusca's user avatar
  • 1,569
5 votes
1 answer
538 views

Spatialite: use of virtual spatial index for distances

I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here http://www.gaia-gis.it/gaia-sins/spatialite-...
mort32's user avatar
  • 51
4 votes
0 answers
3k views

Amazon Redshift: "*SELECT* 1" vs. volt_dt_0 vs. volt_tt_51343b6aa3bd4

I've noticed subqueries in Amazon Redshift can be represented in the explain plan in 3 separate ways: -> XN Subquery Scan "*SELECT* 1" -> XN Subquery Scan volt_dt_0 -> XN Seq Scan on ...
foobar0100's user avatar
4 votes
1 answer
763 views

Unable to send WHERE parameters to subquery

I have the following problem (in PostgreSQL in combination with PostGIS): I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS ...
kwarts's user avatar
  • 49
3 votes
0 answers
1k views

Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?

I run the following query against Postgres 9.5 which returns article information along with the currently valid price: WITH selected_article_rm(id) AS ( VALUES ('80512e52-a406-4a9b-a8df-...
blubb's user avatar
  • 256
3 votes
1 answer
5k views

User variable in nested subquery not getting picked up - in complex sort of 2 tables with gaps

I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a ...
insaner's user avatar
  • 189
2 votes
0 answers
33 views

subquery for history average is slower then expected

I have a MySQL table which contains historic product values. A new record per product is updates every minute. The timestamp field is an INTEGER field with a unix timestamp. There is an index on the ...
eKKiM's user avatar
  • 121
2 votes
0 answers
592 views

Correlated subquery with common table expressions in SQLite

Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
nalzok's user avatar
  • 131
2 votes
1 answer
205 views

How to fetch related rows on multiple condition with ranking

I have following table for storing item information in PostgreSQL. id | name | category | seller | m_age | status | type_id | region | ----------------------------------------------------------...
Rajan M's user avatar
  • 21
2 votes
0 answers
389 views

adding results of InfluxDB subqueries

I'm trying to get the kWh from multiple entities in InfluxDB in Grafana. I have the following raw query. SELECT ("a" + "b") / 3600000 FROM ( SELECT spread("watt_seconds") AS "a" FROM "W" ...
martin blitz's user avatar
2 votes
1 answer
2k views

Having a subquery somehow do the same group by as the outer query

i'm pretty decent with SQL but it's not my main experience of expertise. A colleague asked me a stumper of a question the other day. I was wondering if you guys could provide assistance. Basically we'...
magmusik's user avatar
  • 121
2 votes
0 answers
84 views

Is it possible to replace count distinct inside Correlated sub-queries?

I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)... Is it ...
inux's user avatar
  • 21
2 votes
1 answer
39 views

Consults and counts in multiple databases

I'm not very familiar with mysql advanced queries, because I'm more of a designer as a developer. My problem - I have 3 tables: Clients ------------------------------------------------------ | id | ...
Matt Ross's user avatar
2 votes
0 answers
299 views

Oracle 12c: sql query return less result when it's used in a subquery

I recently encountered a weird situation where i get different result from the same query by only changing its scope. What i mean by changing the scope , is putting the query inside a subquery: I ...
arabische's user avatar
2 votes
0 answers
102 views

Classification of Subqueries?

Just trying to wrap my head around the use of subqueries. Is it correct to say that they come in three main categories: Scalar: returns a single value Table-valued: Returns a table, to compare or add ...
MSIS's user avatar
  • 875
2 votes
1 answer
262 views

Single MySQL query with row averages based on conditions

I have problem, where i know how to proceed with multiple queries, but dont really now I Have a table with BP { name: varchar systolic : int diastolic : int timestamp: date } I need to ...
jasonIM's user avatar
  • 21
2 votes
1 answer
544 views

Delete using join or using sub-query?

I need to delete rows from a table based on what is present in a temporary table. For me, both of these statements work : DELETE from main_table where `id` in (select `deletable_id` from ...
gaganbm's user avatar
  • 141
2 votes
0 answers
917 views

Problem forming a query with subqueries in design view? (MS Access 2010)?

I just started a new job as a dbase admin intern, and due to the database being very large, it is better to do queries in design view, rather than hardcoding the sql. That being said, I cant seem to ...
Pewkie's user avatar
  • 21
2 votes
0 answers
3k views

Postgres Case statement not return else value in this query

I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck... This is my query at ...
Syl's user avatar
  • 121
2 votes
1 answer
951 views

Very slow update with subquery (different tables)

I run this query to update "domains" table based on "log" table: UPDATE domains SET domains.mx_collected = 0 WHERE domains.domain IN (SELECT log.mail as domain_name FROM log WHERE `time` >= ...
gerold's user avatar
  • 21
1 vote
1 answer
19 views

In MS Studio how to SELECT from an existing SQL file

I have a complicated query that select parts from an MRP table using several different criteria. The output of the query is a list of parts which includes the key field [PartNum]. I have saved this ...
TonyG614's user avatar
1 vote
0 answers
52 views

Inconsistent result when running same query on slightly distinct MariaDB versions

I'm investigating the difference in the results given by the same SQL query on two servers running slightly different versions of MariaDB (10.5 and 10.11). Test query is: SELECT a.Name FROM Artist a ...
joanq's user avatar
  • 111
1 vote
0 answers
19 views

postgresql sub query

I have three tables kit - contains kit_id and kit_type (kit type is with android or IOS) appointment - contains appointment_id, created_datetime, kid_id, created_by_id user - contains user_id, user ...
Stephen 's user avatar
1 vote
1 answer
340 views

How to update a foreign key with random values from the referenced table?

In order to generate test data for a postgres database, I want to update a foreign key in a table, let's say 'users' with random values from the referenced table, let's say 'shift'. How to do it? I ...
Edilson Vieira's user avatar
1 vote
0 answers
22 views

query customer retention over range

I am trying to find the best way to accomplish the following. Get the beginning customer count, which carries from the previous day Get New Customer count Get the number of Customers who have not ...
thedemon's user avatar
  • 111
1 vote
1 answer
350 views

Parallel index-only scan with two sub-queries is slow

I'm trying to analyze a slow query that uses parallel index-only scan of two filters (using sub-queries). Specifically I'm the number of 'open_questions' that returned by either subplan1 OR subplan2. ...
Cowabunga's user avatar
  • 135
1 vote
0 answers
185 views

Return back only top N rows for each ID - SQL

I have written this SQL after browsing different topics. The topic I got the solution from -> Limit Records MySQL version: 5.6 SELECT op.* FROM (select op.*, (@rn := if(@s = order_id, @...
olliesyke's user avatar
1 vote
0 answers
407 views

Postgres: one-to-one join with duplicates and inequality matching

** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 ** I have two tables taken from two data sources,...
Harry E.'s user avatar
1 vote
1 answer
455 views

Inconsistent results with MySQL, query never ends on MariaDB

We have some really strange behaviour on MySQL (8.0.29) that we can't explain. We have 2 tables + 1 link table in-between, illustrated by this schema : We run a query whereby we need to fetch the id ...
wimg's user avatar
  • 107
1 vote
1 answer
87 views

Is there anything I can do to optimise this complex query?

I have a rather complex query that spans several layers of subqueries, the two primary ones fetching data from the exact same fields, only one takes the values themselves, the other takes aggregates. ...
theberzi's user avatar
  • 151
1 vote
2 answers
485 views

MySQL - GROUP BY not working with multiple CASE WHEN statements

I am working on a database with Kickstarter database, and I'm trying to: Define short, medium-lengthed, and long campaigns See how much each length of campaign raise Convert different currencies into ...
Ian Yu's user avatar
  • 11
1 vote
0 answers
30 views

subquery returns more tha one row

I want to update data of a table where data-name matches with another table and also one data are common with many items. For example, I have a cycle in which tyre is a part and quantity of tyre is ...
Deepak Kumar's user avatar
1 vote
0 answers
19 views

Why did Codd, et al. prohibit intermediate scalars in SELECT - and how would they have recommended avoiding syntactic repetition?

(I'm asking this because I was reviewing this older question I asked and because I'm currently writing some queries and getting annoyed at how much I need to repeat myself). In ISO-compliant SQL ...
Dai's user avatar
  • 574
1 vote
1 answer
3k views

Converting rows to columns using crosstab in PostgreSQL not working (relation "table" does not exist)

I need to use the compiled data using CTE and then convert the columns to rows using crosstab(open to other ideas) in the next select statement. Below is the query. with checked_adgroup AS ( ...
Ansar Mohiuddin Mohammed's user avatar
1 vote
0 answers
41 views

How to create subquery on multiple result columns?

I have two tables, both sharing the following columns: table inventory: id, key, timestamp table stocks: id, key, timestamp I want to select all occurrences of the key from inventory table together ...
membersound's user avatar
1 vote
0 answers
1k views

Update Quantity Available using Quantity from another table

You will demonstrate an update that includes two tables and uses a subquery. Make sure to prove that your update executed correctly by showing data in the tables before and after the update. ...
Jennifer Payne's user avatar
1 vote
0 answers
91 views

How to get total cost of start package table between two periods by sql query?

Problem: how to get total cost of start package table between two periods with a SQL query? alexaPackage from 28/06/2017 to 05/07/2017 8days cost ? Details I need to get total cost in start ...
abeer shlby's user avatar
1 vote
1 answer
808 views

Do too many subquries create multiple sleeping processes in MySQL?

I am using java & MySQL to make an app. When I check the process list. I see a lot of sleeping processes. It also reaches to a points when no further connection is allowed. I have read many ...
Shekhar Sahu's user avatar
1 vote
0 answers
93 views

How to populate a 'map' table from words of a text?

Having the database structure that follows: CREATE TABLE articles ( article_id int(11) unsigned NOT NULL AUTO_INCREMENT, article text, PRIMARY KEY(article_id) ) ENGINE=InnoDB; CREATE TABLE words ( ...
Googlebot's user avatar
  • 4,531
1 vote
0 answers
1k views

Pass argument to deeply nested query

This overly complex query seems to work when I use a hardcoded value for gid=node.nid (eg. gid=123), but when I use it like below, I get the error following: Unknown column 'node.nid' in 'where ...
Ambidex's user avatar
  • 111
1 vote
0 answers
33 views

Getting Available Date/Times For Attendees And Avoid Conflict

+------------+ +---------------+ +--------------+ | attendees | | attendee_times| | meeting_times| +------------+ +---------------+ +--------------+ | attendee_id| | at_id | | ...
Angry Spartan's user avatar
1 vote
2 answers
5k views

MySQL Correlated Subquery (with multiple columns) per row

MySQL documentation states that: Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) during query execution, so ...
Dan's user avatar
  • 235
1 vote
2 answers
124 views

Help combining two MySQL queries to improve performance

I have two MySQL queries. The first one returns a list of codes, which comprise either one or two letters (e.g. "AB", "WO", "G"). This is the query: SELECT d.field_id_105 AS code, title FROM ...
Pete H's user avatar
  • 11
1 vote
0 answers
225 views

Moving slow sub-query to With clause

I am trying to optimize this query : SELECT sum(a-alias.s_count) AS count FROM a-table a-alias WHERE ( a-alias.created_at_minute_id >= (floor(extract(epoch from( select ...
Ashish Negi's user avatar
1 vote
0 answers
625 views

Update row value with a sub-select join

I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table. The query looks like this (...
shiznatix's user avatar
  • 123
0 votes
1 answer
21 views

How to Do a Sub Query and JOIN for Comparison Report

Hi I was trying to do a Sub Query and Comparison Report For Example, I have a Table of Teams with Color AND Name in a Single Table. A person can be on more than one Team and Multiple Team Colors can ...
user1207381's user avatar
0 votes
0 answers
12 views

Debug RPC Function Creation in Supabase

I've never asked a question before, but I've read what feels like a zillion answers and still can't get my function to work. I'm pretty new to database stuff in general. I'm using Supabase to create ...
Rob's user avatar
  • 1
0 votes
0 answers
25 views

Performance difference in SQL queries using JOINs with function

I have two SQL queries that are structurally the same, but have a significant difference in execution time. The first query takes around 500ms to execute, while the second one takes only 50ms. Both ...
Timon's user avatar
  • 33
0 votes
1 answer
47 views

Combine tables to find date of last value changed

Trying to query the date that a field changed to a zero amount. Here is some of the data from the tables involved: -- mbMaster is the master table for all 'm' type tables (mvTable, mcTable), but does ...
tkmagnet's user avatar
0 votes
0 answers
30 views

How to use a parameter derived from a Table, given I'm filtering the table in query

The task is to find what fraction of total users have logged in at least for one more consecutive day after their first login. SELECT ROUND( COUNT( b.player_id )/COUNT( DISTINCT (SELECT a....
Pixel_Bear's user avatar