Skip to main content

Questions tagged [join]

An SQL join clause combines records from two or more tables or views.

Filter by
Sorted by
Tagged with
127 votes
5 answers
324k views

Best practice between using LEFT JOIN or NOT EXISTS

Is there a best practice between using a LEFT JOIN or a NOT EXISTS format? What is benefit to using one over the other? If none, which should be preferred? SELECT * FROM tableA A LEFT JOIN tableB B ...
Michael Richardson's user avatar
122 votes
1 answer
135k views

Postgres Count with different condition on the same query

EDIT Postgres 9.3 I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2 The current query is working fine which looks like this: Basically it is a 3 table inner ...
jackhammer013's user avatar
120 votes
3 answers
181k views

What is faster, one big query or many small queries?

I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, ...
sudo.ie's user avatar
  • 1,301
81 votes
2 answers
175k views

How to join a table with a table valued function?

I have a user defined function: create function ut_FooFunc(@fooID bigint, @anotherParam tinyint) returns @tbl Table (Field1 int, Field2 varchar(100)) as begin -- blah blah end Now I want to join ...
Shaul Behr's user avatar
  • 2,933
49 votes
8 answers
98k views

Is it possible to mysqldump a subset of a database required to reproduce a query?

Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...
David LeBauer's user avatar
49 votes
4 answers
91k views

Are individual queries faster than joins?

Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient? TL;...
Martin's user avatar
  • 2,420
44 votes
5 answers
109k views

Can I provide a default for a left outer join?

Suppose I have tables a (with column a1) and b (with columns b1 and b2) and I perform a left outer join SELECT * FROM a LEFT OUTER JOIN b ON a.a1 = b.b1 Then b1 and b2 will be NULL where a value of ...
Tom Ellis's user avatar
  • 1,609
44 votes
2 answers
123k views

Outer Apply vs Left Join Performance

I am Using SQL SERVER 2008 R2 I just came across APPLY in SQL and loved how it solves query problems for so many cases, Many of the tables I was using 2 left join to get the result, I was able to ...
Pratyush Dhanuka's user avatar
43 votes
2 answers
3k views

Why does changing the declared join column order introduce a sort?

I have two tables with identically named, typed, and indexed key columns. One of the them has a unique clustered index, the other one has a non-unique. The test setup Setup script, including some ...
Daniel Hutmacher's user avatar
41 votes
2 answers
512k views

Using SELECT in the WHERE clause of another SELECT

I have made a draft remote application on top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functioning of the application. For each final business result that I produce, ...
Stephane Rolland's user avatar
40 votes
1 answer
18k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
Erwin Brandstetter's user avatar
35 votes
4 answers
215k views

How to JOIN two table to get missing rows in the second table

In a simple voting system as CREATE TABLE elections ( election_id int(11) NOT NULL AUTO_INCREMENT, title varchar(255), CREATE TABLE votes ( election_id int(11), user_id int(11), FOREIGN KEYs for ...
Googlebot's user avatar
  • 4,531
35 votes
1 answer
39k views

DELETE rows which are not referenced in other table

I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group. I want to delete all rows from link_group where no related row in ...
Hassan Baig's user avatar
  • 1,979
34 votes
7 answers
51k views

What is the difference between an INNER JOIN and an OUTER JOIN ?

I am new to SQL and wanted to know what is the difference between those two JOIN types? SELECT * FROM user u INNER JOIN telephone t ON t.user_id = u.id SELECT * FROM user u LEFT OUTER JOIN ...
Julien's user avatar
  • 507
34 votes
2 answers
80k views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES ('{"parent":null,"children":[2,3]}'), ('{"parent":1, "children":[4,5]}'), ('{"parent":...
Kokizzu's user avatar
  • 1,373
27 votes
3 answers
150k views

What is more efficient, a where clause or a join with million plus row tables?

We run a website that has 250MM rows in one table and in another table that we join it to for most queries has just under 15MM rows. Sample structures: MasterTable (Id, UserId, Created, Updated...) -...
Jeremy Boyd's user avatar
26 votes
6 answers
3k views

Index performance on ON versus WHERE

I have two tables @T1 TABLE ( Id INT, Date DATETIME ) @T2 TABLE ( Id INT, Date DATETIME ) These tables have a non-clustered index on (Id, Date) And I join these tables SELECT * ...
Erik Bergstedt's user avatar
26 votes
4 answers
150k views

How to get a group where the count is zero?

I'll try to make a graph from the data from my SQL server database. I'll have all streets with the count of the users who are living in this street even the count is zero. For this I've tried this ...
H. Pauwelyn's user avatar
26 votes
2 answers
46k views

Postgres JOIN conditions vs WHERE conditions

Postgres newbie here. I'm wondering if this query is optimized or not? I tried to JOIN ON only the values that are 100% necessary and leaving all the dynamic conditions in the WHERE clause. See ...
Dan's user avatar
  • 467
25 votes
2 answers
10k views

What does [FROM x, y] mean in Postgres?

I'm just getting started with Postgres. Reading this document I came across this query: SELECT title, ts_rank_cd(textsearch, query) AS rank FROM apod, to_tsquery('neutrino|(dark & matter)') query ...
andrerpena's user avatar
24 votes
2 answers
44k views

SQL Server Join/where processing order

After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
Stuart Blackler's user avatar
24 votes
3 answers
87k views

How to join to the same table multiple times?

I have two tables, "hierarchy_table" and "name_table". The hierarchy table contains an object which has multiple parents and children. Each parent and child is referenced by id. | object_id | ...
jase81's user avatar
  • 541
23 votes
2 answers
318k views

Update table using values from another table in SQL Server

I have 2 table in my database. Table #1 ------------------------------------------------------------------------- | name | family | phone | email | gender | phone2 | address | birthdate | -----------...
John Doe's user avatar
  • 361
23 votes
2 answers
8k views

SQL Server does not optimize parallel merge join on two equivalently partitioned tables

Apologies in advance for the very detailed question. I have included queries to generate a full data set for reproducing the problem, and I am running SQL Server 2012 on a 32-core machine. However, ...
Geoff Patterson's user avatar
21 votes
3 answers
9k views

Column name naming conventions and best practices

I would like some expert opinion on best practices when it comes to column naming. The background is that according to Wikipedia, the following syntax, SELECT ... FROM Employees JOIN Timesheets ...
Kerrek SB's user avatar
  • 313
21 votes
3 answers
744 views

Access (Jet) SQL: DateTime stamps in TableB flanking each DateTime stamp in TableA

First Words You can safely ignore the sections below (and including) JOINs: Starting Off if you just want to take a crack of the code. The background and results just serve as context. Please look at ...
mpag's user avatar
  • 968
21 votes
1 answer
21k views

Performance comparison between using Join and Window function to get lead and lag values

I have a table with 20M rows, and each row has 3 columns: time, id, and value. For each id and time, there is a value for the status. I want to know the lead and lag values of a certain time for a ...
Jason's user avatar
  • 683
20 votes
4 answers
39k views

Is it better to separate a big query into multiple smaller queries?

There are situations which require having really big query joining several tables together with sub select statements in them to produce the desired results. My question is, should we consider ...
2hamed's user avatar
  • 323
20 votes
4 answers
346k views

Selecting data from another table using a foreign key [duplicate]

I have two tables A & B. Table A id | name | num Table B id | date | roll id in table B is a foreign key which relates to the id in table A. I want to get the contents of table B where id is ...
Yashas's user avatar
  • 365
19 votes
2 answers
19k views

Index on primary key not used in simple join

I have the following table and index definitions: CREATE TABLE munkalap ( munkalap_id serial PRIMARY KEY, ... ); CREATE TABLE munkalap_lepes ( munkalap_lepes_id serial PRIMARY KEY, ...
András Váczi's user avatar
19 votes
2 answers
9k views

Naming conflict between function parameter and result of JOIN with USING clause

Given this setup in current Postgres 9.4 (from this related question): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') ...
Erwin Brandstetter's user avatar
19 votes
1 answer
15k views

Undesirable Nest Loop vs. Hash Join in PostgreSQL 9.6

I've a trouble with PostgreSQL 9.6 query planning. My query looks like this: SET role plain_user; SELECT properties.* FROM properties JOIN entries_properties ON properties.id = entries_properties....
Yury Lysogorskiy's user avatar
19 votes
3 answers
18k views

Splitting SQL query with many joins into smaller ones helps?

We need to do some reporting every night on our SQL Server 2008 R2. Calculating the reports takes several hours. In order to shorten the time we precalculate a table. This table is created based on ...
Ondrej Peterka's user avatar
18 votes
3 answers
2k views

Strange JOIN ON clause in T-SQL

I am untangling some legacy code and never in all my years have I seen this: Select * FROM GLAccounts INNER JOIN GLCharts ON glaGLChartID = glcGLChartID LEFT JOIN GLCategories ON ...
Joseph Shirk's user avatar
18 votes
3 answers
62k views

MySQL: Which join is better between left outer join and inner join

Which join is better peforming if all of them provides the same result? For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). ...
ursitesion's user avatar
  • 2,071
17 votes
4 answers
69k views

Query to select max value on join

I have a table of Users: |Username|UserType|Points| |John |A |250 | |Mary |A |150 | |Anna |B |600 | and Levels |UserType|MinPoints|Level | |A |100 |...
Lambo Jayapalan's user avatar
17 votes
3 answers
36k views

Does using LIMIT improve the performance and is it noticeable?

I want to understand the following. Assume that I have a complicated query with let's say a join of 5 tables a group by summations and order by. Letting aside any optimizations to the query itself e.g....
Jim's user avatar
  • 761
16 votes
4 answers
3k views

Is a join optimized to a where clause at runtime?

When I write a query like this... select * from table1 t1 join table2 t2 on t1.id = t2.id Does the SQL optimizer, not sure if that is the correct term, translate that to... select * from table1 t1, ...
Mr. Ant's user avatar
  • 263
16 votes
4 answers
59k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
Josh's user avatar
  • 163
16 votes
2 answers
112k views

Update column with data from another table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and I want to update the ...
user3001937's user avatar
16 votes
2 answers
15k views

Cross-product between table column and input values

I seem to be unable to write an SQL query, which computes the cross-product between a table column and a set of given input values. Something along the lines of: WITH {1,2} as Input Select * From ...
user695652's user avatar
16 votes
2 answers
1k views

Peculiar Oracle outer join syntax case

I have seen the following in a query that was supposed to be ported from Oracle outer join syntax to SQL standard outer join syntax: SELECT ... FROM A, B, C, D, E WHERE A.A_ID = B.A_ID AND B.B_ID = C....
Peter Eisentraut's user avatar
16 votes
4 answers
55k views

Casting an array of texts to an array of UUIDs

How can I cast an array of texts into an array of UUIDs? I need to do a join between two tables: users and projects. The users table has an array field named project_ids containing the project IDs ...
Sig's user avatar
  • 383
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 ...
Chris L's user avatar
  • 285
15 votes
5 answers
129k views

Matching single column against multiple values without self-joining table in MySQL

We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data: user_id ...
Christopher Armstrong's user avatar
15 votes
1 answer
39k views

SUM over distinct rows with multiple joins

Schema: CREATE TABLE "items" ( "id" SERIAL NOT NULL PRIMARY KEY, "country" VARCHAR(2) NOT NULL, "created" TIMESTAMP WITH TIME ZONE NOT ...
Stranger6667's user avatar
14 votes
1 answer
11k views

Why does this LEFT JOIN perform so much worse than LEFT JOIN LATERAL?

I have the following tables (taken from the Sakila database): film: film_id is pkey actor: actor_id is pkey film_actor: film_id and actor_id are fkeys to film/actor I am selecting a ...
Jelly Orns's user avatar
13 votes
6 answers
17k views

Why can't I use null values in joins?

I have solved the query problem by using ...row_number() over (partition by... this is a more general question on why we cannot use columns with null values in joins. Why can't a null be equal to a ...
Mark Warner's user avatar
13 votes
3 answers
11k views

Should I use SQL JOIN or IN Clause?

I have a question about best approach. I am not sure which approach is best when data is considered variable in size. Consider the following 3 TABLES: EMPLOYEE EMPLOYEE_ID, EMP_NAME PROJECT ...
rk2010's user avatar
  • 231
13 votes
2 answers
65k views

SQL join query to show rows with non-existent rows in one table

I am trying to get some reporting done for employee time records. We have two tables specifically for this question. Employees are listed in the Members table and each day they enter time entries of ...
farewelldave's user avatar

1
2 3 4 5
41