All Questions

Tagged with
Filter by
Sorted by
Tagged with
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 ...
Your Common Sense's user avatar
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(...
stackover's user avatar
  • 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 ...
Brennan Vincent's user avatar
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 ...
Chetan's user avatar
  • 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 ...
jdotjdot's user avatar
  • 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 ...
whytheq's user avatar
  • 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 ...
dance2die's user avatar
  • 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 ...
Vishal's user avatar
  • 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 ...
roykasa's user avatar
  • 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 ...
Futuretec's user avatar
  • 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 ...
Ricardo's user avatar
  • 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 ...
CharleyXIV's user avatar
  • 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 ...
mikl's user avatar
  • 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 ...
IAdapter's user avatar
  • 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), ...
thomasrutter's user avatar
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 ...
pythonlarry's user avatar
  • 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 ...
Sheldon Ross's user avatar
  • 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 ...
anwarma's user avatar
  • 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....
Hari Rao's user avatar
  • 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, ...
ticallian's user avatar
  • 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, ...
user3182502's user avatar
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 ...
Wernight's user avatar
  • 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). ...
Josh Bond's user avatar
  • 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 | ...
Bantha Fodder's user avatar
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 ...
Ward's user avatar
  • 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 ...
Danmaxis's user avatar
  • 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
Paresh's user avatar
  • 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, ...
Divakar's user avatar
  • 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.
Brad Krusemark's user avatar
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 | ...
Scott's user avatar
  • 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 * ...
João Guilherme's user avatar
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 ...
BHC's user avatar
  • 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 ...
Vance's user avatar
  • 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 ...
Igor's user avatar
  • 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 ......
Phrogz's user avatar
  • 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 (...
Bart van Heukelom's user avatar
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(...
Lukas Eder's user avatar
  • 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 ...
Gothmog's user avatar
  • 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 ...
obautista's user avatar
  • 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: ...
MaxGeek's user avatar
  • 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 ...
Techie's user avatar
  • 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 ...
user3054901's user avatar
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 ...
jordan's user avatar
  • 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 ...
Chris's user avatar
  • 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 ...
Eray's user avatar
  • 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 ...
cjh193's user avatar
  • 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 ...
Colleen's user avatar
  • 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 ...
eddy edu's user avatar
  • 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 ...
Cris McLaughlin's user avatar
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 ...
qwerty's user avatar
  • 223

1
2 3 4 5
129