An SQL join clause combines records from two or more tables or views.
24
votes
8answers
8k 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 ...
20
votes
1answer
937 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
761 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 = ...
9
votes
3answers
2k 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
...
9
votes
3answers
1k 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 ...
9
votes
3answers
178 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
1k 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, ...
7
votes
3answers
363 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)
);
...
6
votes
2answers
2k 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...) ...
6
votes
3answers
1k 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
173 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 ...
6
votes
1answer
176 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
2answers
1k 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 ...
5
votes
3answers
227 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 ...
5
votes
5answers
10k 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 ...
5
votes
6answers
6k 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
...
5
votes
1answer
429 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 ...
5
votes
2answers
613 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
2answers
334 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
2answers
90 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
...
5
votes
2answers
248 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
1k 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
3answers
174 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 ...
4
votes
1answer
188 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
2answers
464 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
3answers
1k 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
64 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
2answers
573 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,
...
3
votes
3answers
226 views
How can I select all online friends?
I'm currently working on a social network software written in PHP & MySQL, and it's all pretty fine, but one thing I'm stuck about is online friends. My tables looks following
Users
...
3
votes
2answers
218 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 ...
3
votes
3answers
307 views
SQL JOIN Syntax in MS SQL
I have been taught in my MSSQL classes, this is how to join two tables
select * from FirstTable A
JOIN SecondTable B
on A.ID= B.ID
Now in my professional life, I came across JOIN queries like ...
3
votes
3answers
413 views
CROSS JOIN with temp table and CASE statement
I'm trying to CROSS JOIN a table with a view. Here is my code:
SELECT *
FROM ( SELECT * ,
CASE TypeId
WHEN 1 THEN InGs828S
WHEN ...
3
votes
1answer
328 views
Subqueries run very fast individually, but when joined are very slow
ypercube solved the problem. Subqueries were totally unnecessary, and the whole thing works with plain joins. It is still strange that MySQL's optimizer could not make use of my original query, ...
3
votes
1answer
226 views
Build a three table join with a recusive table in the middle?
I have three relevent tables: Parts, PartGroup, and MarkupGroup.
Parts is simple.
PartID artificial primary key
Part part number
PartGroupID Foreign key
sample data:
1 ...
3
votes
2answers
194 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 ...
3
votes
1answer
86 views
Oracle: Non key-preserved table should be
I'm getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" when I try to update a join. I've searched around the site and found a lot of advice on what key-preserved ...
3
votes
1answer
73 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 ...
3
votes
1answer
2k 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 ...
3
votes
1answer
1k views
Having trouble with Merge Join with SSIS
I am currently using Merge Join to perform an inner join on two sorted sets of data in SSIS. The problem I am having is that no records are being returned from the inner join. To troubleshoot I have ...
3
votes
2answers
255 views
How do I calculate working business minutes?
I'm working on business inteligence reports and one of my task is to create reports for time tracking.
I have to report on business minutes (10 hours per day) within business week (monday to ...
3
votes
1answer
151 views
Complex SQL Query
I'm trying to formulate a single query to return a nice list of a host's interfaces and the interfaces attributes from the Cacti MySQL DB. Here are the tables I am referencing:
host_snmp_cache:
...
3
votes
1answer
1k views
how to fetch different data on compare rows of two tables
I have two tables in my database, one is user_app table in which user info are stored, table are as:
+--------------+--------------+------+-----+-------------------+-----------------------------+
| ...
3
votes
1answer
307 views
How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')
Consider a simple query as
SELECT * FROM posts
JOIN tags USING(post_id)
JOIN tag_map USING(tag_id)
WHERE tags.tag IN('tag1', 'tag2', 'tag3')
tag_map is a many-to-many relationship between tables ...
3
votes
1answer
63 views
Get data from two tables with recursive relationships
I have a MySQL 5.1 DB, being used behind a PHP application to track permissions and other items that need to be issued to staff that hold various positions within an org. To track what privileges a ...
2
votes
3answers
169 views
Is it better to seperate a big query into multiple smaller queries?
There are situations which require to have really big query joining several tables together with sub select statements in them to produce the desired results.
My question is, should we consider using ...
2
votes
1answer
338 views
Query takes hours
Up until now all queries in my PostgreSQL application were fast. During the last days, sometimes a query takes several hours. Index are healthy, because after dump, restore, vacuum -z, it is still the ...
2
votes
5answers
523 views
Does 'Cities' deserve a separate table?
I have the following Customers table:
customer_id - int
company_name - nvarchar
street - nvarchar
city - nvarchar
comments - nvarchar
The app will only be used in part of one small country ...
2
votes
3answers
622 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 ...
2
votes
2answers
2k views
forcing Oracle to use hash join for a subquery
I have a query that looks like
SELECT *
FROM table0
WHERE id IN (SELECT id FROM table1 JOIN table2)
Oracle is choosing to join table0 with the result of (table1 x table2) using nested loops and ...
2
votes
3answers
229 views
Express union which is dependent on other results
I would like to make a union of two tables that are actually in a hierarchical constellation.
How would I write that in relational algebra? Say table A is the parent of B in a 1:n relationship.
...
