An SQL join clause combines records from two or more tables or views.
Questions tagged [join]
2017 questions
129
votes
5 answers
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
ON A.idx = B.idx
WHERE B.idx IS NULL
SELECT…
Michael Richardson
- 1,465
- 2
- 10
- 9
127
votes
1 answer
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 join. I did not make this query but the developer…
jackhammer013
- 1,469
- 2
- 12
- 11
123
votes
3 answers
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, we only need to use only 1 column.
So would it be faster…
sudo.ie
- 1,331
- 2
- 9
- 5
83
votes
2 answers
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 this on another table, like so:
select f.ID,…
Shaul Behr
- 2,963
- 8
- 34
- 42
50
votes
8 answers
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
Is there a way that I can incorporate this select…
David LeBauer
- 3,162
- 8
- 32
- 34
50
votes
4 answers
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;DR: If my joined query takes longer than running…
Martin
- 2,420
- 4
- 26
- 35
45
votes
5 answers
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 a1 has no matching value of b1.
Can I provide a…
Tom Ellis
- 1,639
- 3
- 16
- 14
44
votes
2 answers
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 get in 1 outer apply.
I have small amount of data in…
Pratyush Dhanuka
- 559
- 1
- 5
- 9
43
votes
2 answers
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 realistic statistics:
DROP TABLE IF EXISTS…
Daniel Hutmacher
- 9,173
- 1
- 27
- 52
41
votes
2 answers
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, it happens that I call something like 40 select…
Stephane Rolland
- 8,911
- 11
- 33
- 40
40
votes
1 answer
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 *
FROM a
JOIN b USING (a_id)
Just in more complex…
Erwin Brandstetter
- 185,527
- 28
- 463
- 633
37
votes
2 answers
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 link_reply exists. Sounds basic enough but I've been…
Hassan Baig
- 2,079
- 8
- 31
- 44
36
votes
2 answers
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":1, "children":[]}'),
('{"parent":2, …
Kokizzu
- 1,403
- 6
- 18
- 35
35
votes
4 answers
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 getting the list of elections a user has voted, the…
Googlebot
- 4,551
- 26
- 70
- 96
34
votes
7 answers
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 telephone t ON t.user_id = u.id
When should I use one or…
Julien
- 507
- 1
- 4
- 7