All Questions
6,403
questions
1114
votes
20
answers
637k
views
Join vs. sub-query
I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why.
I lack the theoretical knowledge to judge for ...
- 157k
548
votes
8
answers
705k
views
updating table rows in postgres using subquery
I have this table in a postgres 8.4 database:
CREATE TABLE public.dummy
(
address_id SERIAL,
addr1 character(40),
addr2 character(40),
city character(25),
state character(2),
zip character(...
- 6,655
515
votes
3
answers
1.0m
views
Nested select statement in SQL Server
Why doesn't the following work?
SELECT name FROM (SELECT name FROM agentinformation)
I guess my understanding of SQL is wrong, because I would have thought this would return the same thing as
...
- 11k
445
votes
28
answers
473k
views
Remove duplicate rows in MySQL
I have a table with the following fields:
id (Unique)
url (Unique)
title
company
site_id
Now, I need to remove rows having same title, company and site_id. One way to do it will be using the ...
- 4,915
322
votes
5
answers
316k
views
What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
Since PostgreSQL came out with the ability to do LATERAL joins, I've been reading up on it since I currently do complex data dumps for my team with lots of inefficient subqueries that make the overall ...
- 16.6k
309
votes
4
answers
235k
views
Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
In this excellent SO question, differences between CTE and sub-queries were discussed.
I would like to specifically ask:
In what circumstance is each of the following more efficient/faster?
CTE
...
- 35.1k
194
votes
10
answers
116k
views
Difference between CTE and SubQuery?
From this post How to use ROW_NUMBER in the following procedure?
There are two versions of answers where one uses a sub-query and the other uses a CTE to solve the same problem.
Now then, what is ...
- 36.5k
152
votes
8
answers
207k
views
Which of the join and subquery queries would be faster and why? When I should prefer one over the other?
I have a join query
Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id
and a subquery query
Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept)
Which would be faster ...
- 12.3k
151
votes
3
answers
276k
views
subquery in FROM must have an alias
I have this query I have written in PostgreSQL that returns an error saying:
[Err] ERROR:
LINE 3: FROM (SELECT DISTINCT (identifiant) AS made_only_recharge
This is the whole query:
SELECT COUNT ...
- 2,977
143
votes
7
answers
260k
views
How can I insert values into a table, using a subquery with more than one result?
I have two tables in SQL Server:
article
prices
Now I want to select a certain set of ids and insert some entries into the prices-table with those ID.
e.g. (wrong and not working SQL)
INSERT INTO ...
- 1,441
133
votes
5
answers
448k
views
How to do a Postgresql subquery in select clause with join in from clause like SQL Server?
I am trying to write the following query on postgresql:
select name, author_id, count(1),
(select count(1)
from names as n2
where n2.id = n1.id
and t2.author_id = t1.author_id
...
- 7,855
130
votes
2
answers
502k
views
SQL LEFT JOIN Subquery Alias
I'm running this SQL query:
SELECT wp_woocommerce_order_items.order_id As No_Commande
FROM wp_woocommerce_order_items
LEFT JOIN
(
SELECT meta_value As Prenom
FROM wp_postmeta
...
- 1,610
121
votes
9
answers
190k
views
MySQL DELETE FROM with subquery as condition
I am trying to do a query like this:
DELETE FROM term_hierarchy AS th
WHERE th.parent = 1015 AND th.tid IN (
SELECT DISTINCT(th1.tid)
FROM term_hierarchy AS th1
INNER JOIN term_hierarchy ...
- 24.1k
109
votes
4
answers
246k
views
How to delete from select in MySQL?
This code doesn't work for MySQL 5.0, how to re-write it to make it work
DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 ))
I want to delete columns that dont ...
- 63.4k
86
votes
8
answers
30k
views
Can you define "literal" tables in SQL?
Is there any SQL subquery syntax that lets you define, literally, a temporary table?
For example, something like
SELECT
MAX(count) AS max,
COUNT(*) AS count
FROM
(
(1 AS id, 7 AS count),
...
- 116k
78
votes
4
answers
58k
views
Return pre-UPDATE column values using SQL only
I posted a related question, but this is another part of my puzzle.
I would like to get the OLD value of a column from a row that was UPDATEd - WITHOUT using triggers (nor stored procedures, nor any ...
- 2,326
76
votes
6
answers
130k
views
Efficient latest record query with Postgresql
I need to do a big query, but I only want the latest records.
For a single entry I would probably do something like
SELECT * FROM table WHERE id = ? ORDER BY date DESC LIMIT 1;
But I need to pull ...
- 5,474
74
votes
2
answers
170k
views
Find duplicate entries in a column [duplicate]
I am writing this query to find duplicate CTN Records in table1. So my thinking is if the CTN_NO appears more than twice or higher , I want it shown in my SELECT * statement output on top.
I tried ...
- 2,045
70
votes
3
answers
50k
views
Difference between WITH clause and subquery?
What is the difference between WITH clause and subquery?
1. WITH table_name as ( ... )
2. select *
from ( select curr from tableone t1
left join tabletwo t2
on (t1....
- 3,130
69
votes
3
answers
70k
views
MySQL: Returning multiple columns from an in-line subquery
I'm creating an SQL statement that will return a month by month summary on sales.
The summary will list some simple columns for the date, total number of sales and the total value of sales.
However, ...
- 1,641
68
votes
5
answers
279k
views
Postgres Error: More than one row returned by a subquery used as an expression
I have two separate databases. I am trying to update a column in one database to the values of a column from the other database:
UPDATE customer
SET customer_id=
(SELECT t1 FROM dblink('port=5432, ...
- 711
60
votes
9
answers
93k
views
How to reuse a result column in an expression for another result column
Example:
SELECT
(SELECT SUM(...) FROM ...) as turnover,
(SELECT SUM(...) FROM ...) as cost,
turnover - cost as profit
Sure this is invalid (at least in Postgres) but how to achieve the same ...
- 37k
58
votes
1
answer
142k
views
Use results from one sql query in another where statement (subquery?)
I see many similar questions but they're either so complex I can't understand them, or they don't seem to be asking the same thing.
It's simple: I have two columns: users (dmid) and downloads (dfid).
...
- 1,769
54
votes
3
answers
56k
views
PostgreSQL: Sub-select inside insert
I have a table called map_tags:
map_id | map_license | map_desc
And another table (widgets) whose records contains a foreign key reference (1 to 1) to a map_tags record:
widget_id | map_id | ...
- 1,280
54
votes
2
answers
90k
views
Why is there a HUGE performance difference between temp table and subselect
This is a question about SQL Server 2008 R2
I'm not a DBA, by far. I'm a java developer, who has to write SQL from time to time. (mostly embedded in code). I want to know if I did something wrong ...
- 2,822
53
votes
2
answers
20k
views
Postgresql - Using subqueries with alter sequence expressions
Is it possible to use subqueries within alter expressions in PostgreSQL?
I want to alter a sequence value based on a primary key column value.
I tried using the following expression, but it wouldn't ...
- 1,740
50
votes
3
answers
62k
views
How we can use CTE in subquery in sql server?
How we can use a CTE in a subquery in SQL Server?
like:
SELECT id (I want to use CTE here), name FROM table_name
- 3,739
49
votes
8
answers
316k
views
Difference between Subquery and Correlated Subquery
Is the following piece of SQL Query a normal query or a Correlated Subquery ??
SELECT UserID,
FirstName,
LastName,
DOB,
GFName,
GLName,
LoginName,
...
- 652
45
votes
3
answers
52k
views
When to use SQL sub-queries versus a standard join?
I am working on rewriting some poorly written SQL queries and they are over-utilizing sub-queries. I am looking for best-practices regarding the use of sub-queries.
Any help would be appreciated.
- 513
44
votes
2
answers
69k
views
Selecting max record for each user
This seems if it should be fairly simple, but I'm stumbling in trying to find a solution that works for me.
I have a member_contracts table that has the following (simplified) structure.
MemberID | ...
- 907
42
votes
5
answers
100k
views
Use columns from the main query in the subquery
Is there any way to get a column in real time, from a main query, and use it in a subquery?
Something like this: (Use A.item in the subquery)
SELECT item1, *
FROM TableA A
INNER JOIN
(
select * ...
- 503
42
votes
2
answers
58k
views
Equivalent of ON CONFLICT DO NOTHING for UPDATE postgres
I want to update rows in my postgres database if the updated version wouldn't violate the primary key constraint. If it would, I want to leave the row as it is.
Assuming the table has primary keys on ...
- 919
41
votes
3
answers
145k
views
Joining 2 SQL SELECT result sets into one
I've got 2 select statements, returning data like this:
Select 1
col_a col_b
Select 2
col_a col_c
If I do union, I get something like
col_a col_b
And rows joined. What i need is getting it ...
- 413
40
votes
5
answers
51k
views
In which sequence are queries and sub-queries executed by the SQL engine?
Hello I made a SQL test and dubious/curious about one question:
In which sequence are queries and sub-queries executed by the SQL engine?
the answers was
primary query -> sub query -> sub sub query ...
- 2,639
38
votes
9
answers
44k
views
WHERE col1,col2 IN (...) [SQL subquery using composite primary key]
Given a table foo with a composite primary key (a,b), is there a legal syntax for writing a query such as:
SELECT ... FROM foo WHERE a,b IN (SELECT ...many tuples of a/b values...);
UPDATE foo SET ......
- 300k
34
votes
4
answers
47k
views
Count rows with a specific condition in aggregate query
I have this query to get the number of PlayerSessions with reconnect = TRUE, grouped by Player.country:
SELECT
country,
COUNT(*) AS with_reconnect
FROM PlayerSession S LEFT JOIN Player P ON (...
- 43.7k
32
votes
2
answers
83k
views
Example of an Oracle PIVOT clause with subquery
Oracle's definition of the PIVOT clause specifies that there is a possibility to define a subquery in the IN clause. A fictional example of what I would imagine this to be is this
... PIVOT (AVG(...
- 216k
31
votes
2
answers
44k
views
Is possible to reuse subqueries?
I'm having some problems trying to perform a query. I have two tables, one with elements information, and another one with records related with the elements of the first table. The idea is to get in ...
- 881
30
votes
3
answers
37k
views
sql server sub query with a comma separated resultset
I need to return records on a table and my result set needs to contain a comma separated list.
I have attached an image of the 3 tables. I need to do a select that returns the record in the first ...
- 3,665
28
votes
10
answers
110k
views
T-SQL Subquery Max(Date) and Joins
I'm trying to join multiple tables, but one of the tables has multiple records for a partid with different dates. I want to get the record with the most recent date.
Here are some example tables:
...
- 1,105
28
votes
5
answers
55k
views
Subqueries with EXISTS vs IN - MySQL
Below two queries are subqueries. Both are the same and both works fine for me. But the problem is Method 1 query takes about 10 secs to execute while Method 2 query takes under 1 sec.
I was able to ...
- 44.9k
28
votes
2
answers
140k
views
How does Subquery in select statement work in oracle
I have looked all over for an explanation, to how does the subquery in a select statement work and still I cannot grasp the concept because of very vague explanations.
I would like to know how do you ...
- 367
27
votes
5
answers
80k
views
SQL Like with a subquery
How can i make this work?
SELECT *
FROM item
WHERE item_name LIKE '%'
|| (SELECT equipment_type
FROM equipment_type
...
- 3,516
27
votes
2
answers
66k
views
SQL use column from subselect in where clause
I have a query that looks something like that:
SELECT a, b, c,
(SELECT d from B limit 0,1) as d
FROM A
WHERE d >= 10
I get the result that I want when I run the query without the whereclause ...
- 3,057
26
votes
6
answers
50k
views
MySQL #1093 - You can't specify target table 'giveaways' for update in FROM clause
I tried:
UPDATE giveaways SET winner = '1' WHERE ID = (SELECT MAX(ID) FROM giveaways)
But it gives:
#1093 - You can't specify target table 'giveaways' for update in FROM clause
This article ...
- 7,088
25
votes
3
answers
33k
views
How to select most frequent value in a column per each id group?
I have a table in SQL that looks like this:
user_id | data1
0 | 6
0 | 6
0 | 6
0 | 1
0 | 1
0 | 2
1 | 5
1 | 5
1 | 3
1 | 3
1 | 3
1 ...
- 329
25
votes
2
answers
6k
views
SQL query to look for a Kevin Bacon number of 2
Using the IMDB database, I have tables actor, casts, and movie, and I need to select actors with a Kevin Bacon number of 2. I thought this should do it, but I'm getting 0 rows returned. What is my ...
- 24.7k
23
votes
5
answers
74k
views
How to limit results of a LEFT JOIN
Take the case of two tables: tbl_product and tbl_transaction.
tbl_product lists product details including names and ids while tbl_transaction lists transactions involving the products and includes ...
- 233
22
votes
3
answers
20k
views
How do I get a count of associated rows in a left join in MySQL?
I have two tables, a vehicle table with columns:
id
stock
year
make
model
and an images table with columns:
id
vehicle_id
name
caption
default tinyint(1)
I am trying to list the vehicle's ...
- 1,201
22
votes
7
answers
51k
views
MYSQL use 'LIKE' in 'WHERE' clause to search in subquery
How would you use 'LIKE' to search in a subquery?
E.g. i've tried doing this, but doesn't work:
SELECT *
FROM mytable
WHERE name
LIKE '%
(SELECT name FROM myothertable)
%'
I ...
- 223