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.

learn more… | top users | synonyms

11
votes
1answer
225 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 ...
8
votes
3answers
674 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 ...
7
votes
2answers
2k 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 ...
7
votes
2answers
4k 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 ...
5
votes
4answers
1k 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 ...
5
votes
3answers
566 views

What could be the reason for disallowing a sub query in the values clause?

For example SQL> create table dates(d date); Table created. SQL> insert into dates select sysdate from dual; 1 row created. SQL> select * from dates; D --------- 28-MAY-11 SQL> ...
5
votes
1answer
669 views

Mysql sub query returns more than 1 row

SELECT * FROM wp_posts WHERE ID IN ( (SELECT courses FROM wp_category WHERE CatID =401) OR (SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) ...
4
votes
2answers
906 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 ...
4
votes
3answers
284 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 ...
4
votes
2answers
6k 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.priority ASC, LIMIT 1) AS special_price, ( SELECT ps.date ...
4
votes
1answer
65 views

does a subquery pull the entire table?

i am attempting to run a subquery in a table: SELECT t1.*, t2.* FROM t1 LEFT JOIN (SELECT * FROM table2 GROUP BY col1) AS t2 on t1.col1 = t2.col1 WHERE ... My main question is, since that subquery ...
4
votes
1answer
4k 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 ...
4
votes
1answer
46 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 ( ...
3
votes
3answers
294 views

How can I select using the same fields from an undetermined number of tables using MySQL?

I'm dealing with a MySQL database where I have an undetermined amount of identically structured tables that look like this: foo_reference1 foo_reference2 foo_referencea foo_referenceb .... ...
3
votes
3answers
5k views

How to convert a Top 1 subquery using outer table alias to Oracle?

I have the following SQL Server query select (select top 1 b2 from BB b where b.b1 = a.a1 order by b2) calc, a1, a2 from AA a where a2 = 2; which I can rewrite using analytic functions ...
3
votes
1answer
2k 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, ...
3
votes
1answer
186 views

How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans?

Server A and Server B have identical hardware and instance configurations (A is Production, B is QA). B's DBs were restored from A's backups from one week ago. I was provided this query by the ...
3
votes
5answers
355 views

Query optimization [Oracle]

The Query: select JOIN_RESULT.batch_id as BATCH_ID, JOIN_RESULT.unique_doc_id as DOCUMENT_ID, JOIN_RESULT.brand as BRAND, JOIN_RESULT.message_date_time as UPLOAD_DATE, ...
3
votes
3answers
7k views

How to select/count rows where the same value exists in a column but not in other column all at the same table

I have this table structure and data: CREATE TABLE IF NOT EXISTS `default_relations_users` ( `id_user_rq` int(11) NOT NULL, `id_user_ap` int(11) NOT NULL, UNIQUE KEY `rusers_rq_ap_idx` ...
3
votes
2answers
388 views

MySQL cache is not getting hit by subqueries

I noticed something weird on MySQL query_cache behavior and I would like to know if this is a normal behavior. Let's say I have an item table ID | Item ----------- 1 | Item_1 2 | Item_2 3 | ...
3
votes
1answer
3k 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: ...
3
votes
1answer
147 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 ...
3
votes
1answer
2k views

SELECT LIMIT 1 per column value?

Lets say I have the following table ----------------------------- | user_id | comment | ----------------------------- | 2 | thats cool | | 2 | awesome | | 3 | ...
3
votes
1answer
4k 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 ...
3
votes
2answers
215 views

Do MySQL sub-queries essentially use as much overhead as separate queries?

If I have a query like this: SELECT name FROM ( SELECT * FROM `table` WHERE id IN (1,40,300) ORDER BY AnotherColumn ) From a bandwidth and overhead standpoint, is that the same thing as doing ...
3
votes
2answers
2k 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 ...
2
votes
2answers
411 views

Why do I need to use a sub query to filter down a grouped select?

If I do this -- SELECT dv.Name ,MAX(hb.[DateEntered]) as DE FROM [Devices] as dv INNER JOIN [Heartbeats] as hb ON hb.DeviceID = dv.ID WHERE DE < '2013-03-04' GROUP BY dv.Name ...
2
votes
1answer
27 views

Update only the oldest entry in a table with foreign key criteria

I'm working on a forum website. What I am trying to do is updating the thread Title and Content (first post), which resides in different tables - "Threads" and "Posts". "Threads" "Posts" I'm ...
2
votes
1answer
335 views

How to speed up a query with a lot of subqueries

I have a query which uses a series of functions to return the status of each document for a loan. SELECT loan_number, borrower_name, get_application_status(loan_number, ...
2
votes
2answers
239 views

pull records that are inactive

I need help with this query. I have records in customer table Main table - M +---------+---------+----------+ | company | Account | Active | +---------+---------+----------+ | A | A123 | ...
2
votes
1answer
1k views

SQL Server select with regex

Can we use regular expression to select the item from database? The table item is like below Table column|name| 10.01.02 | a | 100.2.03 | b | 1021.10.04 | c | Now my problem is that i ...
2
votes
1answer
910 views

Select multiple columns in subquery

I am having trouble selecting all cloumns in a sub query and can't seem to get no where with joining for replacement. http://sqlfiddle.com/#!2/2481e/1 The link is to the schema with my current ...
2
votes
2answers
141 views

Query taking too much time

My query is taking too much time to execute. Please help me here. mysql> explain select pcm.catalog_id from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c ...
2
votes
1answer
73 views

Merge queries into one

The following 2 queries are working as expected and they show the count. Is it possible to merge them into single query? I need sum_hits columns to be part of the rest of the column shown in the ...
2
votes
2answers
149 views

Order by sub select

I have 3 tables: articles, paragraphs, notes An article may have many paragraphs, and a paragraph may have 0 or 1 note. Now I want to find all notes belong to an article: select * from notes where ...
2
votes
1answer
52 views

Find the second highest value from a subquery or derived table

I have a subquery which returns the following results. 'c003', '120000.00' 'c002', '90000.00' 'c001', '20000.00' 'c005', '8000.00' 'c004', '5000.00' I want to find the second highest value from ...
2
votes
1answer
483 views

Best practice: Unions or a derived table?

I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so: CREATE TABLE `pending` ( ... `invoice` int(11) DEFAULT NULL, `lid` int(11) DEFAULT ...
2
votes
1answer
494 views

Selecting minimum value using a subquery

I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating. We have a table for prices ...
2
votes
3answers
1k views

SQL Subquery , One to Multiple rows

Following query I have written returns the result as expected, example 121350 rows. SELECT dbo.Articles_in_Consignment.Consignment_id, dbo.ORDER_D.Orders_boxsize, ...
2
votes
3answers
1k views

How do i subtract a number of days from a prepared statement parameter in db2?

I have a C# application using OLEDB to connect to a DB2 database. Basically, I want to do this: SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS , SUM(CALC.SUBTOTAL) AS REVENUE FROM ( ...
2
votes
0answers
293 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 ...
2
votes
1answer
230 views

Does MySQL have problems with nested insert like with subqueries in where?

Query 1: INSERT `personal`.`locations` SELECT DISTINCT `s`.* FROM `references` `t` JOIN `locations` `s` ON `first_id` = `s`.`id` WHERE `lat` >= 37.3 AND `lat` <= ...
1
vote
2answers
3k views

MySQL: Delete all but last N records

Consider the following table: mysql> DESCRIBE pixels; +---------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default ...
1
vote
2answers
82 views

optimization of expensive subquery

The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units: SELECT reports.* FROM reports INNER JOIN ...
1
vote
1answer
542 views

Postgresql nested row_to_json not working

This probably wouldn't be an issue if I were using postgresql 9.2, however I can't (yet) upgrade from 9.1, and I'm using the json data type extension for 9.1. I'm trying to achieve a json object like ...
1
vote
1answer
162 views

Querying multiple tables

I am working on a query from our ticketing system, and I am having problems figuring out the best way to join these tables together. I have the following tables/fields: SR_service: stores ticket ...
1
vote
2answers
133 views

How to restructure this slow query

For the following query: SELECT l.* FROM `leads` AS `l` INNER JOIN `users` AS `u` ON l.client_id = u.id WHERE (`l`.`lender_id` = 1) AND (l.claimed_date IS NULL) AND (`l`.`disabled` = 0) AND ...
1
vote
1answer
66 views

Postgres, levenshtein distance and nested queries

I have a DB Table with 3,000,000 rows or more like that: |ID|T4|T16|T64|TL where ID =INTEGER (PK) and T4,T16,T64,TL are text fields. Each one is larger in length than the previous, i.e., LENGTH(T4) ...
1
vote
1answer
123 views

UPDATE a column by COUNT of another table

It might be a naive question, but what is the difference of these two queries and which is preferred? UPDATE table1, (SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B SET table1.Freq = ...
1
vote
1answer
50 views

How to UPDATE a column by COUNT of FK from another table?

How to extend this query for the entire table, to update all ids? UPDATE table1 SET number=( SELECT COUNT(*) FROM table2 where id=1 ) WHERE id=1 id is the PRIMARY KEY in table1 and FK in table2. ...