[Link]

asp

SQL JOIN
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a
relationship between certain columns in these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each
primary key value must be unique within the table. The purpose is to bind data together, across tables,
without repeating all of the data in every table.
Look at the "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can
have the same P_Id. The P_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers
to the persons in the "Persons" table without using their names.
Notice that the relationship between the two tables above is the "P_Id" column.

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences
between them.
• JOIN: Return rows when there is at least one match in both tables
• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left
table
• FULL JOIN: Return rows when there is a match in one of the tables
SQL INNER JOIN Keyword
The INNER JOIN keyword return rows when there is at least one match in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: INNER JOIN is the same as JOIN.

SQL INNER JOIN Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Now we want to list all the persons with any orders.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link]
The result-set will look like this:
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
The INNER JOIN keyword return rows when there is at least one match in both tables. If there are
rows in "Persons" that do not have matches in "Orders", those rows will NOT be listed.

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no
matches in the right table (table_name2).

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL LEFT JOIN Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Now we want to list all the persons and their orders - if any, from the tables above.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link]
The result-set will look like this:
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove
The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches
in the right table (Orders).

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no
matches in the left table (table_name1).

SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL RIGHT JOIN Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Now we want to list all the orders with containing persons - if any, from the tables above.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link]
The result-set will look like this:
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
34764
The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no
matches in the left table (Persons).

SQL FULL JOIN Keyword

The FULL JOIN keyword return rows when there is a match in one of the tables.

SQL FULL JOIN Syntax

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SQL FULL JOIN Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Now we want to list all the persons and their orders, and all the orders with their persons.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link]
The result-set will look like this:
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove
34764
The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the
right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are
rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.

########-----####
[Link]
[Link]

Inner, Outer, Left, Right, Cross(Full) Join Nedir ?

SQL Sorgulama dilinde birden fazla tabloyu birleştirme yada ilgili başka tablodan veri çekmek için
Inner, Outer, Left, Right, Cross Join Segmentleri kullanılır.
Bunlar aşağıda belirtildiği şekilde kullanılır.
Çok basit olarak inner join bir-e-bir join anlamına gelmektedir. İki tablonuz olduğunu düşünün TabloA
da Alan1 adlı bir alanınız var bu alanda A,B,C değerleri var,
TabloB de Alan1 de ise A,B,K değerleri var.
Eğer iki tablo arasında inner join kurarsanız sonuç ta dönecek listede sadece A ve B değerleri olacaktır.
Çünkü inner joinde yanlızca eşit olan kayıtlar döner.

01 SELECT TabloA.Alan1 as TabloAAlan1 , TabloB.Alan1 as TabloBAlan1

02 FROM TabloA INNER JOIN TabloB
03 ON TabloA.Alan1 = TabloB.Alan1
04 /*
05 Kodun MSSQL de sade şekli şudur :
06 */
07 SELECT TabloA.Alan1, TabloB.Alan1 as TabloBAlan1
08 FROM TabloA , TabloB
09 WHERE TabloA.Alan1 = TabloB.Alan1
10 /*
11 Dönen Sonuç :
12 TabloAAlan1 TabloBAlan1
13 A A
14 B B
15 */

Çok basit olarak outer bir-e-çoklu join anlamına gelmektedir (MsSQL Server da çok-a-çoklu join de
kurulabilmektedir). İki tablonuz olduğunu düşünün TabloA da Alan1 adlı bir alanınız var bu alanda
A,B,C değerleri var, TabloB de Alan1 de ise A,B,K değerleri var.
Eğer iki tablo arasında outer join kuracaksınız ve sizin için TabloA daki tüm kayıtlar TabloB de ise
sadece TabloA da bulunanlar gelir.
Left Outer Join
Join kelimesinin sol tarafındaki tabloyu baz alarak eklemek için kullanılır.

01 SELECT TabloA.Alan1 as TabloAAlan1, TabloB.Alan1 as TabloBAlan1

02 FROM TabloA LEFT OUTER JOIN TabloB
03 ON TabloA.Alan1 = TabloB.Alan1
04 /*
05 Bu kodun MSSQL de sade şekli şudur :
06 */
07 SELECT TabloA.Alan1 as TabloAAlan1, TabloB.Alan1 as TabloBAlan1
08 FROM TabloA , TabloB
09 WHERE TabloA.Alan1 *= TabloB.Alan1
10 /*
11 Dönen Sonuç :
12 TabloAAlan1 TabloBAlan1
13 A A
14 B B
15 C Null
16 */

Right Outer Join

Join kelimesinin sağ tarafındaki tabloyu baz alarak eklemek için kullanılır.

01 SELECT TabloA.Alan1 as TabloAAlan1, TabloB.Alan1 as TabloBAlan1

02 FROM TabloA RIGHT OUTER JOIN TabloB
03 ON TabloA.Alan1 = TabloB.Alan1
04 /*
05 Bu kodun MSSQL de sade şekli şudur :
06 */
07 SELECT TabloA.Alan1 as TabloAAlan1, TabloB.Alan1 as TabloBAlan1
08 FROM TabloA , TabloB
09 WHERE TabloA.Alan1 =* TabloB.Alan1
10 /*
11 Dönen Sonuç :
12 TabloAAlan1 TabloBAlan1
13 A A
14 B B
15 Null K
16 */
Full Outer Join (MySQL için Cross Outer Join)
Her iki tablodaki tüm verileri getirmek için kullanılır.

01 SELECT TabloA.Alan1 as TabloAAlan1, TabloB.Alan1 as TabloBAlan1

02 FROM TabloA FULL OUTER JOIN TabloB
03 ON TabloA.Alan1 = TabloB.Alan1
04 /*
05 Bu kodun MSSQL de sade şekli şudur :
06 */
07 SELECT TabloA.Alan1 as TabloAAlan1, TabloB.Alan1 as TabloBAlan1
08 FROM TabloA , TabloB
09 WHERE TabloA.Alan1 =* TabloB.Alan1
10 /*
11 Dönen Sonuç :
12 TabloAAlan1 TabloBAlan1
13 A A
14 B B
15 Null K
16 C Null
17 */
Join’ nin sade halinde kullanılan “*” karakteri ise sadece Ms SQL Server’a özeldir. Oracle da ise bu
karakter yerine “(+)” işareti kullanılmaktadır ve alan isimlerin sonuna bir boşluk bırakarak yazılır.
Örnek olarak : WHERE TabloA.Alan1 = TabloB.Alan1 (+)
OUTER cümlesi kullanılmasada olur. (Left Join gibi)

##---##
An inner join is the most common join operation used in applications and can be regarded as the
default join-type. Inner join creates a new result table by combining column values of two tables (A and
B) based upon the join-predicate. The query compares each row of A with each row of B to find all
pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for
each matched pair of rows of A and B are combined into a result row. The result of the join can be
defined as the outcome of first taking the Cartesian product (or Cross join) of all records in the tables
(combining every record in table A with every record in table B)—then return all records which satisfy
the join predicate.

An outer join does not require each record in the two joined tables to have a matching record. The
joined table retains each record—even if no other matching record exists. Outer joins subdivide further
into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the
rows from (left, right, or both).
(In this case left and right refer to the two sides of the JOIN keyword.)
No implicit join-notation for outer joins exists in standard SQL.
[Link]
#####---##
Oracle9i
Starting with Oracle9i, the confusing outer join syntax using the ‘(+)’ notation has been superseded by
ISO 99 outer join syntax. As we know, there are three types of outer joins, left, right, and full outer
join. The purpose of an outer join is to include non-matching rows, and the outer join returns these
missing columns as NULL values.
Let’s review the syntax differences between these variations in join syntax:
Left outer join: Oracle8i
select
last_name,
department_name
from
employees e,
departments d
where
e.department_id = d.department_id(+);

Left outer join: Oracle9i

select
last_name,
department_name
from
employees e
left outer join
departments d
on
e.department_id = d.department_id;

[Link]

#####---##