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

learn more… | top users | synonyms

26
votes
8answers
21k 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 ...
22
votes
1answer
3k 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 * ...
13
votes
2answers
959 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 = ...
12
votes
7answers
16k 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 ...
12
votes
2answers
2k 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, ...
11
votes
3answers
3k 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 ...
11
votes
1answer
281 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 ...
10
votes
3answers
4k 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 ...
10
votes
3answers
200 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 ...
8
votes
2answers
4k 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 ...
7
votes
3answers
5k 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? ...
7
votes
2answers
7k 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...) ...
7
votes
3answers
3k 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 ...
7
votes
3answers
1k 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
204 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
1answer
247 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 ...
6
votes
3answers
261 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 ...
6
votes
5answers
26k 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 ...
6
votes
3answers
4k 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
1answer
628 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 ...
6
votes
3answers
1k 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
269 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 ...
5
votes
3answers
4k 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 ...
5
votes
2answers
1k 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, ...
5
votes
2answers
2k 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
846 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
569 views

Unexpected extremely long query time (~5 minutes using nested WHEN-INs)

For the first time, I am running up against a horrifically long execution time of a MySQL query (~5 minutes). The data in the database is highly (and not arbitrarily) normalized. It very efficient ...
5
votes
3answers
5k 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), ...
5
votes
1answer
4k views

Query to compare two subsets of data from the same table?

My problem: I'm trying to compare to subsets of data within one table, and I've got two methods that partially work and one certainty that there's got to be a more correct way to do it. The idea ...
5
votes
1answer
421 views

Build a three table join with a recusive table in the middle?

I have three relevant tables: Parts, PartGroup, and MarkupGroup. Parts is simple. PartID artificial primary key Part part number PartGroupID Foreign key sample data: 1 ...
5
votes
2answers
135 views

mysql join by 2 ways - how it works? - which gives performance difference

I had 2 different ways to query the content which showed performance difference when executed. The 1st way is EXPLAIN select SOME_COLUMNS from ( select * from A where CONDITION ) p inner join ( ...
5
votes
2answers
463 views

How to use merge hints to isolate complex queries in SQL Server

I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number ...
4
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 ...
4
votes
2answers
1k 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 ...
4
votes
2answers
3k 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 ...
4
votes
1answer
423 views

NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior

My actual work query was an inner join, but this simple example with cross join seems to nearly always reproduce the problem. SELECT * FROM ( SELECT 1 UNION ALL SELECT 2 ) AA ( A ) CROSS JOIN ...
4
votes
1answer
5k 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 ...
4
votes
2answers
429 views

Multiple JOIN of 6 tables via 5 database instances

I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the ...
4
votes
2answers
166 views

Group_by and having vs Joins when table is self joined 5-6 times

Now the situation due to which this question came to me. Schema is Table User_Read_Book user_id | book_id Now I want to get users who have read certain books. Say give me users who ...
4
votes
2answers
693 views

Right Full Outer Join Query

I came across 1 query which is Select * from R Natural Outer Join S Where R=(A,B) has tuples {(1,2),(1,2),(3,4)} and S=(B,C) has tuples {(2,5),(2,5),(4,6),(7,10)}. To implement this I created 2 ...
4
votes
1answer
198 views

MySQL - GROUP_CONCAT returns duplicate data, can't use DISTINCT

I have a normalized database and I'm trying to return data from multiple tables using JOINs and GROUP_CONCAT. Problem: Rows are being duplicated with GROUP_CONCAT. I can't use DISTINCT because some ...
4
votes
1answer
507 views

Forcing Join Order

I have a table with just over a million rows. These rows can have a parent record in the same table which is found by joining to itself on 6 different columns (i.e. there is no single ParentID ...
4
votes
2answers
717 views

Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that's in 2000 (80) compatibility mode

We have a very old database running on SQL Server 2008 R2. The database is running in 2000 (80) compatibility mode and has oodles of NON-ANSI joins in stored procedures, views and functions. We need ...
4
votes
4answers
493 views

Table join to match without duplicates

I have a situation where I need to match arrivals and departures of assets in a location. The problem is that asset movements are not always entered into the database chronologically, nor is there ...
4
votes
1answer
5k views

mySQL query optimisation — multiple joins or select … where not in (select distinct…)?

Background I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
4
votes
3answers
2k views

Query to normalize table/combine row text

I have a table (call it oldTable) with columns like so: ID (int),Rank (int),TextLineNumber (int),SomeText (varchar) The primarykey is multi-part: ID+Rank+TextLineNumber. I'm trying to ...
4
votes
1answer
219 views

Is there a standard way to replace codes with values from a lookup table for reporting or analytics?

Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel ...
4
votes
1answer
184 views

How to write this self join based on three columns

Hello there I have a following table ------------------------------------------ | id | language | parentid | no_daughter | ------------------------------------------ | 1 | 1 | 0 | ...
3
votes
4answers
813 views

Self Join? Or am I wrong?

We have a table that we use to store customers and items. We need to be able to find customers that have bought one item and have not bought another item: customer item Sally 1 Sally ...
3
votes
3answers
4k views

Alternative query to this (avoid DISTINCT)

Note: I work with MSSQL 2008, but I guess it's valid for many others DB engines I have this table "Users": UserID User CountryID 1 user 1 1 2 user 2 2 3 user 3 3 4 user ...