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

learn more… | top users | synonyms

28
votes
8answers
35k 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 ...
28
votes
1answer
5k 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 * ...
21
votes
4answers
21k 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? ...
17
votes
3answers
401 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 ...
17
votes
2answers
4k 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, ...
16
votes
7answers
33k 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 ...
13
votes
2answers
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 = ...
13
votes
3answers
4k 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 ...
12
votes
1answer
418 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 ...
11
votes
3answers
6k 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 ...
11
votes
1answer
191 views

How to optimize a query so that it seeks on one index first, and then another index after that

I have two sets of earth measurements from satellite data, each with time fields (mjd for mean julian date) and geography positions (GeoPoint, spacial) and I'm looking for coincidences between the two ...
10
votes
5answers
55k 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 ...
10
votes
3answers
6k 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 ...
10
votes
2answers
9k 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 ...
10
votes
3answers
204 views

Are there any database engines which will intuit the join condition based on the existing foreign keys?

It seems strange to me that, when I've defined a foreign key, the engine cannot use this information to automatically figure out the correct JOIN expressions, but instead requires me to re-type the ...
9
votes
2answers
21k 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...) ...
9
votes
1answer
419 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') ...
8
votes
3answers
10k 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 ...
8
votes
4answers
407 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 ...
8
votes
2answers
14k 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 ...
8
votes
2answers
4k views

How can row estimates be improved in order to reduce chances of spills to tempdb

I notice that when there are spill to tempdb events (causing slow queries) that often the row estimates are way off for a particular join. I've seen spill events occur with merge and hash joins and ...
8
votes
1answer
290 views

Combine two event tables into a single timeline

Given two tables: CREATE TABLE foo (ts timestamp, foo text); CREATE TABLE bar (ts timestamp, bar text); I wish to write a query that returns values for ts, foo, and bar that represents a unified ...
8
votes
2answers
241 views

Running a complex query for every date in a range

I've got a table of orders Column | Type | Modifiers ...
8
votes
2answers
567 views

Should I nest dependent outer joins in SQL Server?

I have heard mixed information on this and am hoping for a canonical or expert opinion. If I have multiple LEFT OUTER JOINs , each dependent on the last, is it better to nest them? For a contrived ...
7
votes
3answers
271 views

What does the output of a JOIN statement look like?

I've been wanting to use joins for a while, but I'm having trouble visualizing the output so I know how to put it to use. Let's say I have 2 tables: CREATE TABLE Cities ( id INT UNSIGNED PRIMARY ...
7
votes
2answers
4k 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, ...
7
votes
3answers
2k views

Oracle's left join and where clauses errors

CREATE TABLE "ATABLE1" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); CREATE TABLE "ATABLE2" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); ...
7
votes
1answer
924 views

Why can't we perform joins in a distributed database like Bigtable?

From this answer to What database does Google use?, BigTable is not a relational database. It does not support joins nor does it support rich SQL-like queries. Is this obvious? I do not ...
7
votes
1answer
7k 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 ...
7
votes
1answer
220 views

Are relations slower than a big, inefficient table?

I've been asked in my job to violate the first normal form (repeating groups across columns, using empty / null values) several times, "for the sake of computer processing power". In a nutshell, a ...
7
votes
2answers
117 views

Most efficient way to generate a diff

I have a table in SQL server that looks like this: Id |Version |Name |date |fieldA |fieldB ..|fieldZ 1 |1 |Foo |20120101|23 | ..|25334123 2 |2 |Foo ...
7
votes
1answer
20k views

Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening. Following up on my previous ...
7
votes
5answers
25k views

Full Outer Join in MS Access

I have two staff lists: List A: StaffID Supervisor ==================== 0001234 NULL 0001235 0001234 0001237 0001234 0001239 0001237 and List B: StaffID Supervisor ...
7
votes
1answer
309 views

Table order in Join Query

if i say: Table1 left join Table2 is it the same as saying Table2 right join Table1 ? In other words, should i expect to get the same results from 2 identical queries where the only thing that is ...
7
votes
3answers
7k views

Can someone explain why left joining two views in mysql is so slow?

Here is a question I asked yesterday - http://stackoverflow.com/questions/22180727/left-joining-two-views-is-slow. I got a good answer that helped me but I don't understand why the LEFT JOIN is so ...
6
votes
2answers
85k 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, ...
6
votes
5answers
455 views

SQL queries to only show most recent purchase records for individual food items

I'm working with a food purchasing / invoice system in MS Access 2013 and am trying to create an SQL query that will return the most recent purchase price for each individual food item. Here is a ...
6
votes
2answers
35k 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 ...
6
votes
3answers
10k views

Why is this Full Outer Join not working?

I've used Full Outer Joins before to get my desired results, but maybe I don't fully understand the concept because I am not able to accomplish what should be a simple join. I have 2 tables (which ...
6
votes
2answers
1k views

SQL ANSI JOIN precedence

I have a query that looks like this: SELECT * FROM TBLA A LEFT JOIN TBLB B ON A.Col1 = B.Col2 RIGHT JOIN TBLC C ON B.Col3 = C.Col4 JOIN TBLD D ON C.Col5 = D.Col6 In which order ...
6
votes
2answers
424 views

MySQL: delete…where..in( ) vs delete..from..join, and locked tables on delete with subselect

Disclaimer: please excuse my lack of knowledge about database internals. Here it goes: We run an application (not written by us) which has a big performance problem in a periodic cleanup job in the ...
6
votes
3answers
18k views

How to SELECT multiple COUNT(*) columns with each GROUP BY having a different WHERE clause?

I have the following schema: CREATE TABLE Person ( PersonId int PRIMARY KEY ) CREATE TABLE Action ( ActionId int PRIMARY KEY, PersonId int NOT NULL FOREIGN KEY REFERENCES Person(PersonId), ...
6
votes
1answer
58 views

Postgresql join removal

I just watched the very interesting video from Rob Farley "Designing for simplification" and I was wondering if the 4 rules given in the video for join removal also hold on PostgreSQL. If a view is ...
5
votes
6answers
8k views

How to find the nth highest salary in SQL?

I have found a query to find the nth highest salary from Employee table , but i don't understand the logic of (N-1)? EmpID Salary 1 90000 2 80000 3 54000 4 ...
5
votes
3answers
725 views

Can I skip the intermediate table in a join?

I've written this query : SELECT "BD - Utilizadores".Utilizador, "BD - Utilizadores"."Palavra passe", "BD - Áreas".área FROM "BD - Utilizadores" INNER JOIN "BD - Permissões" on ...
5
votes
2answers
3k views

The order of INNER JOIN's

I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000. I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
5
votes
3answers
2k views

Optimizing query using view on EAV structure

An application is writing into a database that follows an EAV structure, similar to this: CREATE TABLE item ( id INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE item_attr ( item ...
5
votes
3answers
2k views

Many-to-Too-Many Relationship

I'm working on a book database. And among others, I have the following two tables (a bit simplified for the question): tblBook: ID, Title tblPerson: ID, Name Note that the tblPerson table contains ...
5
votes
2answers
93 views

Removing duplicate rows with two or more left join tables

With a query like this SELECT a.id, a.name, COALESCE( json_agg(b.*), '[]'::json ), COALESCE( json_agg(c.*), '[]'::json ), FROM a LEFT JOIN b ON a.id = b.a_id LEFT JOIN c ON a.id = ...
5
votes
2answers
9k views

MySQL not using indexes when joining against another table

I have two tables, the first table contains all articles / blog posts within a CMS. Some of these articles may also appear in a magazine, in which case they have a foreign key relationship with ...