A join is a general operation in relational algebra for a combining operation on two or more relations in a relational database system. `JOIN` is also keyword of the SQL language for performing a join operation.
84
votes
11answers
31k views
Explicit vs implicit SQL joins
Is there any efficiency difference in an explicit vs implicit inner join?
For example:
select * from
table a inner join table b
on a.id = b.id;
vs.
select a.*, b.*
from table a, table b
where a.id ...
137
votes
10answers
85k views
INNER JOIN ON vs WHERE clause
For simplicity, assume all relevant fields are NOT NULL.
You can do:
SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1, table2
WHERE
table1.foreignkey = table2.primarykey
...
101
votes
10answers
48k views
Join vs. subquery
I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query and I hate it, don't know why.
I lack of theoretical knowledge to judge for myself ...
792
votes
10answers
397k views
14
votes
5answers
2k views
SQL JOIN: is there a difference between USING, ON or WHERE?
I was wondering if there is any difference in the way SQL performs on these join statements:
SELECT * FROM a,b WHERE a.ID = b.ID
SELECT * FROM a JOIN b ON a.ID = b.ID
SELECT * FROM a JOIN b ...
93
votes
5answers
99k views
122
votes
4answers
46k views
How to join data frames in R (inner, outer, left, right)?
Given two data frames
df1 = data.frame(CustomerId=c(1:6),Product=c(rep("Toaster",3),rep("Radio",3)))
df2 = data.frame(CustomerId=c(2,4,6),State=c(rep("Alabama",2),rep("Ohio",1)))
> df1
...
73
votes
11answers
105k views
SQL left join vs multiple tables on FROM line?
Most SQL dialects accept both the following queries:
SELECT a.foo, b.foo
FROM a, b
WHERE a.x = b.x
SELECT a.foo, b.foo
FROM a
LEFT JOIN b ON a.x = b.x
Now obviously when you need an outer join, ...
37
votes
3answers
2k views
Why is LINQ JOIN so much faster than linking with WHERE?
I've recently upgraded to VS 2010 and am playing around with LINQ to Dataset. I have a strong typed dataset for Authorization that is in HttpCache of an ASP.NET WebApplication.
So i wanted to know ...
98
votes
13answers
32k views
Subqueries vs joins
I refactored a slow section of an application we inherited from another company to use an inner join instead of a subquery like
where id in (select id from ... )
The refactored query runs about ...
127
votes
4answers
49k views
Rails :include vs. :joins
This is more of a "why do things work this way" question rather than a "I don't know how to do this" question...
So the gospel on pulling associated records that you know you're going to use is to ...
38
votes
4answers
26k views
Full Outer Join in MySQL
I want to do a Full Outer Join in MySQL. Is this possible? Is a Full Outer Join supported by MySQL?
95
votes
6answers
129k views
SQL join: where clause vs. on clause
After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins.
The answer may be related (or even the same) but the question is different.
What is the difference and what should go in ...
117
votes
9answers
37k views
What is the difference between Left, Right, Outer and Inner Joins?
I am wondering how to differentiate all these different joins ...
170
votes
9answers
314k views
SQL update from one Table to another based on a ID match
I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers.
I created a view ...
