How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
Yes, it depends on the situation you are in.
Why use SQL JOIN?
Answer: Use the SQL JOIN whenever multiple tables must be accessed through an SQL SELECT statement and no results should be returned if there is not a match between the JOINed tables.
Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.
Also check this post: SQL SERVER – Better Performance – LEFT JOIN or NOT IN?.
Find original one at: Difference between JOIN and OUTER JOIN in MySQL.
What kind of join do I need?
You would use an INNER
join to establish the relationship between the common gameid
field;
select
votes.userid,
games.title
from games
inner join votes on (votes.gameid = game.gameid)
where
votes.userid = 'a'
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?
Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.
Also check this post: SQL SERVER – Better Performance – LEFT JOIN or NOT IN?.
Find original one at: Difference between JOIN and OUTER JOIN in MySQL.
SQL JOIN and different types of JOINs
What is SQL JOIN
?
SQL JOIN
is a method to retrieve data from two or more database tables.
What are the different SQL JOIN
s ?
There are a total of five JOIN
s. They are :
1. JOIN or INNER JOIN
2. OUTER JOIN
2.1 LEFT OUTER JOIN or LEFT JOIN
2.2 RIGHT OUTER JOIN or RIGHT JOIN
2.3 FULL OUTER JOIN or FULL JOIN
3. NATURAL JOIN
4. CROSS JOIN
5. SELF JOIN
1. JOIN or INNER JOIN :
In this kind of a JOIN
, we get all records that match the condition in both tables, and records in both tables that do not match are not reported.
In other words, INNER JOIN
is based on the single fact that: ONLY the matching entries in BOTH the tables SHOULD be listed.
Note that a JOIN
without any other JOIN
keywords (like INNER
, OUTER
, LEFT
, etc) is an INNER JOIN
. In other words, JOIN
is
a Syntactic sugar for INNER JOIN
(see: Difference between JOIN and INNER JOIN).
2. OUTER JOIN :
OUTER JOIN
retrieves
Either,
the matched rows from one table and all rows in the other table
Or,
all rows in all tables (it doesn't matter whether or not there is a match).
There are three kinds of Outer Join :
2.1 LEFT OUTER JOIN or LEFT JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the
right table. If there are no columns matching in the right table, it returns NULL
values.
2.2 RIGHT OUTER JOIN or RIGHT JOIN
This JOIN
returns all the rows from the right table in conjunction with the matching rows from the
left table. If there are no columns matching in the left table, it returns NULL
values.
2.3 FULL OUTER JOIN or FULL JOIN
This JOIN
combines LEFT OUTER JOIN
and RIGHT OUTER JOIN
. It returns rows from either table when the conditions are met and returns NULL
value when there is no match.
In other words, OUTER JOIN
is based on the fact that: ONLY the matching entries in ONE OF the tables (RIGHT or LEFT) or BOTH of the tables(FULL) SHOULD be listed.
Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.
3. NATURAL JOIN :
It is based on the two conditions :
- the
JOIN
is made on all the columns with the same name for equality. - Removes duplicate columns from the result.
This seems to be more of theoretical in nature and as a result (probably) most DBMS
don't even bother supporting this.
4. CROSS JOIN :
It is the Cartesian product of the two tables involved. The result of a CROSS JOIN
will not make sense
in most of the situations. Moreover, we won't need this at all (or needs the least, to be precise).
5. SELF JOIN :
It is not a different form of JOIN
, rather it is a JOIN
(INNER
, OUTER
, etc) of a table to itself.
JOINs based on Operators
Depending on the operator used for a JOIN
clause, there can be two types of JOIN
s. They are
- Equi JOIN
- Theta JOIN
1. Equi JOIN :
For whatever JOIN
type (INNER
, OUTER
, etc), if we use ONLY the equality operator (=), then we say that
the JOIN
is an EQUI JOIN
.
2. Theta JOIN :
This is same as EQUI JOIN
but it allows all other operators like >, <, >= etc.
Many consider both
EQUI JOIN
and ThetaJOIN
similar toINNER
,OUTER
etcJOIN
s. But I strongly believe that its a mistake and makes the
ideas vague. BecauseINNER JOIN
,OUTER JOIN
etc are all connected with
the tables and their data whereasEQUI JOIN
andTHETA JOIN
are only
connected with the operators we use in the former.Again, there are many who consider
NATURAL JOIN
as some sort of
"peculiar"EQUI JOIN
. In fact, it is true, because of the first
condition I mentioned forNATURAL JOIN
. However, we don't have to
restrict that simply toNATURAL JOIN
s alone.INNER JOIN
s,OUTER JOIN
s
etc could be anEQUI JOIN
too.
Difference between JOIN and INNER JOIN
They are functionally equivalent, but INNER JOIN
can be a bit clearer to read, especially if the query has other join types (i.e. LEFT
or RIGHT
or CROSS
) included in it.
What kind of JOIN do I need to perform this type of query?
You can cross join to safety tests from cars - then left join to TestResults - the cross join ensures you get the 10 x 20 matrix, the left join gives you the results
Use ISNULL or COALESCE to replace nulls in the result set with zeroes
e.g.
SELECT car.name, testresults.testname, isnull(testresults.result, 0) FROM
cars CROSS JOIN
safetytests
LEFT JOIN testresults on safetytests.testid = testresults.testid AND car.id = testresults.carid
Did a sqlfiddle as someone suggested :) real good that site
http://sqlfiddle.com/#!3/2bc73/2
What is the difference between INNER JOIN and OUTER JOIN ?
Assuming you're joining on columns with no duplicates, which is a very common case:
An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.
Examples
Suppose you have two tables, with a single column each, and data as follows:
A B
- -
1 3
2 4
3 5
4 6
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.
Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
select * from a INNER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4
Left outer join
A left outer join will give all rows in A, plus any common rows in B.
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a = b.b(+);
a | b
--+-----
1 | null
2 | null
3 | 3
4 | 4
Right outer join
A right outer join will give all rows in B, plus any common rows in A.
select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a(+) = b.b;
a | b
-----+----
3 | 3
4 | 4
null | 5
null | 6
Full outer join
A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b;
a | b
-----+-----
1 | null
2 | null
3 | 3
4 | 4
null | 6
null | 5
Which kind of join do I need to use here?
Looks like a cross join
:
select y.field1, x.field2
from x cross join
y;
What kind of JOIN or WHERE clause do I need?
Did you try this:
SELECT * FROM Messages m
INNER JOIN Employee e
INNER JOIN Departmnet d
ON ((m.sender=e.employeeId || m.receiver=e.employeeId) d.departmentId=e.departmentId)
WHERE m.sender = '5' OR m.receiver = '5'
What kind of join, if any, is appropriate here?
Try this Query.
select
S.userName,
S.firstName,
S.lastName,
A.actionDetails,
A.actionDate
from Departments D
join Staff S on d.departmentID=s.departmentID
join Actions A on s.staffID=A.staffID
WHERE D.departmentID = YourDeparmentID;
Types Of JOIN in SQl
Related Topics
Concatenate Results from a SQL Query in Oracle
Compute Percents from Sum() in the Same Select SQL Query
Insert Command :: Error: Column "Value" Does Not Exist
How to Design a Database for User Defined Fields
Dynamic Oracle Pivot_In_Clause
Is It Necessary to Create Tables Each Time You Connect the Derby Database
Add Unique Constraint to Combination of Two Columns
Transfer Data from One Database to Another Database
How to Return Multiple Rows from the Stored Procedure? (Oracle Pl/Sql)
How to Convert Comma Separated Nvarchar to Table Records in SQL Server 2005
SQL Between Clause with Strings Columns
SQL Server Select to JSON Function
Polymorphism in SQL Database Tables
Splitting Delimited Values in a SQL Column into Multiple Rows