Inner Join pada View SQL Server

Inner Join adalah clause untuk membuat view dimana dua buah tabel dihubungkan melalui satu atau lebih field dari masing-masing tabel. Sebagai contoh adalah tabel PEMBELIAN dan tabel SUPLIER dapat di-join-kan melalui field KODE_SUPLIER.Berikut adalah contoh view dari gabungan tabel MT_SUPLIER dan tabel PEMBELIAN:

1. SELECT dbo.MT_SUPLIER.KODE_SUPLIER, dbo.MT_SUPLIER.NAMA_SUPLIER, dbo.PEMBELIAN.TANGGAL,  

2. dbo.PEMBELIAN.TOTAL_HARGA_BELI FROM dbo.MT_SUPLIER INNER JOIN dbo.PEMBELIAN ON  

3. dbo.MT_SUPLIER.KODE_SUPLIER = dbo.PEMBELIAN.KODE_SUPLIER  

Perintah select tersebut akan menghasilkan view dengan output KODE_SUPLIER,NAMA_SUPLIER, TANGGAL dan TOTAL_HARGA_BELI dari gabungan tabel MT_SUPLIER dan tabelPEMBELIAN.Jika anda ingin mengurutkan data berdasarkan filed tertentu maka perintah select harus diikuit oleh ORDER BY nama_field. Misalnya anda ingin mengurutkan data yang ditampilkan berdasarkan NAMA_SUPLIER maka perintah SELECT query menjadi sebagai berikut:

1. SELECT TOP 100 PERCENT dbo.MT_SUPLIER.KODE_SUPLIER, dbo.MT_SUPLIER.NAMA_SUPLIER,  

2. dbo.PEMBELIAN.TANGGAL, dbo.PEMBELIAN.TOTAL_HARGA_BELI FROM dbo.MT_SUPLIER INNER JOIN  

3. dbo.PEMBELIAN ON dbo.MT_SUPLIER.KODE_SUPLIER = dbo.PEMBELIAN.KODE_SUPLIER ORDER BY  

4. dbo.MT_SUPLIER.NAMA_SUPLIER  

Left Join dan Right Join pada View SQL Server

Left Join atau Left Outer Join adalah clause untuk membuat view dari dua buah tabel yang dihubungkan melalui satu atau lebih field dari masing-masing tabel dimana tabel yang disebutkan pertama akan menampilkan semua data walaupun pada tabel kedua data tersebut tidak ada.

Misalnya tabel MT_ANGGOTA mempunyai data sebabai berikut:

NO_ANGGOTA NAMA ALAMAT

12.31 ANDI HIDAYAT JL. BUAHBATU 10

12.32 YUSUF HADI JL. PASIRJADI 12

12.33 FERY RUSLAN JL. AHMAD YAHI 112

12.34 DEDI DAHLAN JL. GAJAH MADA 12

Tabel PENJUALAN mempunyai data sebagai berikut:

NO_SLIP TANGGAL NO_ANGGOTA

1342 1-Feb-2010 12.31

1343 2-Feb-2010 12.33

1344 3-Feb-2010 12.33

1345 4-Feb-2010 12.34

Perintah Select query dengan Left Join sebagai berikut:

view plain copy to clipboard print ?

1. SELECT  dbo.MT_ANGGOTA.NO_ANGGOTA, dbo.MT_ANGGOTA.NAMA,  2. dbo.PENJUALAN.NO_SLIP, dbo.PENJUALAN.TANGGAL  3. FROM dbo.MT_ANGGOTA LEFT OUTER JOIN  4. dbo.PENJUALAN ON dbo.MT_ANGGOTA.NO_ANGGOTA = dbo.PENJUALAN.

NO_ANGGOTA  5. ORDER BY dbo.MT_ANGGOTA.NAMA  

Akan menghasilkan output sebagai berikut:

NO_ANGGOTA NAMA NO_SLIP TANGGAL

12.31 ANDI HIDAYAT 1342 1-Feb-2010

12.34 DEDI DAHLAN 1345 4-Feb-2010

12.33 FERY RUSLAN 1343 2-Feb-2010

12.33 FERY RUSLAN 1344 3-Feb-2010

12.32 YUSUF HADI

Pada baris data kelima untuk data YUSUF HADI pada kolom NO_SLIP dan TANGGAL datanya kosong atau null karena memang tidak ada data penjualan untuk NO_ANGGOTA 12.32.

Jika Clause LEFT JOIN atau LEFT OUTER JOIN diganti dengan INNER JOIN, maka output view yang dihasilkan adalah:

NO_ANGGOTA NAMA NO_SLIP TANGGAL

12.31 ANDI HIDAYAT 1342 1-Feb-2010

12.34 DEDI DAHLAN 1345 4-Feb-2010

12.33 FERY RUSLAN 1343 2-Feb-2010

12.33 FERY RUSLAN 1344 3-Feb-2010

Data YUSUF HADI tidak akan muncul karena caluse INNER JOIN hanya akan menampilkan data NO_ANGGOTA yang ada pada kedua tabel.

Clausa Right Join hampir sama dengan Left Join perbedaanya adalah pada clausa Right Join tabel yang datanya ditampilkan semua adalah tabel yang disebutkan terakhir.

Beberapa model Query di SQL Server dalam seleksi data menggunakan JOIN, EXIST, IN, ANY dan SOME

Februari 10, 2010 oleh ndereklangkung

Perintah Exists biasanya digunakan untuk check keberadaan suatu suatu obyek di suatu database. Namun dalam tulisan kali ini saya lebih menekankan untuk check apakah data / record yang ada di sebuah tabel juga ada di tabel yang lain.

Biasanya, untuk keperluan di atas, saya menggunakan query join. Untuk menambah perbendaharaan perintah SQL Query, berikut saya sertakan beberapa perintah dalam SQL yang menghasilkan data yang sama.

Untuk mencobanya, sebelumnya saya buat 2 buah tabel (Table_A dan Table_B).

CREATE TABLE [dbo].[Table_A]([id] [int] IDENTITY(1,1) NOT NULL,[nama] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table_B]([id] [int] IDENTITY(1,1) NOT NULL,[nama] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO

Selanjutnya, Table_A saya isi dengan data sebagai berikut :

id | Nama————-1 | Meja2 | Kursi3 | Lemari4 | Piring5 | Gelas6 | Mangkuk7 | Sendok8 | Garpu

dan Table_B saya isi dengan data sebagai berikut :

id | Nama————-1 | Sendok2 | Garpu3 | Sapu4 | Keset5 | Piring6 | Gelas7 | Pintu8 | Jendela

Dari kedua tabel diatas, tampak bahwa keduanya memiliki beberapa kesamaan data / record. Untuk seleksi data yang ada di Table_A dan juga ada di Table_B, biasanya saya menggunakan query sebagai berikut :

SELECT A.id, A.nama FROM Table_A AS AINNER JOIN Table_B AS B ON A.nama = B.nama

Untuk mendapatkan hasil yang sama, kita menggunakan model query lain sebagai berikut :

menggunakan EXIST

SELECT * FROM Table_A AS aWHERE EXISTS(SELECT nama FROM Table_B AS bWHERE a.nama = b.nama)

menggunakan IN

SELECT * FROM Table_A AS aWHERE nama IN(SELECT nama FROM Table_B AS b)

menggunakan = ANY

SELECT * FROM Table_A AS aWHERE nama = ANY(SELECT nama FROM Table_B AS b)

menggunakan = SOME

SELECT * FROM Table_A AS aWHERE nama = SOME(SELECT nama FROM Table_B AS b)

Sedangkan untuk seleksi data yang ada di Table_A tetapi tidak ada di Table_B, kita bisa menggunakan beberapa query sebagai berikut :

SELECT * FROM Table_A AS aWHERE NOT EXISTS(SELECT nama FROM Table_B AS bWHERE a.nama = b.nama)

atau

SELECT * FROM Table_A AS aWHERE nama NOT IN(SELECT nama FROM Table_B AS b)

atau

SELECT * FROM Table_A AS aWHERE nama != ALL(SELECT nama FROM Table_B AS b)

JOIN

JOIN adalah teknik yang digunakan untuk mengakses lebih dari satu tabel danmenggabungkan hasilnya. Perhatikan tabel sesi di bawah ini

1

2

3

SELECT nim, mahasiswa.nama, grade

FROM kuliah, mahasiswa

WHERE kuliah.nim=mahasiswa.nim

perintah diatas dapat juga di tulis menggunakan INNER JOIN

1

2

3

SELECT nim, mahasiswa.nama, grade

FROM kuliah INNER JOIN mahasiswa

ON kuliah.nim=mahasiswa.nim

Alias digunakan untuk menyederhanakan tulisan, agar tidak terlalu panjang.

1

2

3

SELECT nim, mahasiswa.nama, grade

FROM kuliah, mahasiswa

WHERE kuliah.nim=mahasiswa.nim

Penulisan dapat disederhanakan menjadi :

1

2

3

SELECT nim, M.nama,grade

FROM kuliah A ,mahasiswa M

WHERE A.nim = M.nim

Alias dapat juga di berikan pada INNER JOIN :

1

2

3

SELECT nim, M.nama, grade

FROM kuliah A INNER JOIN mahasiswa B

ON A.nim = B.nim

Join biasa dilakukan pada 2 buah tabel atau lebih.

A join is used to combine columns from two or more tables into a single result set. To join data from two tables you write the names of two tables in the FROM clause along with JOIN keyword and an ON phrase that specifies the join condition. The join condition indicates how two tables should be compared. In most cases they are compares on the base on the relationship of primary key of the first table and foreign key of the second table. In this article I will tell you about three important joins.

1.       Inner Join2.       Outer Join

I have two tables - Vendor table and Advance table. This is how my database tables and data looks like. I will be using these tables in my samples below.

Vendor table:

Figure 1.

Advance table:

Figure 2.

Now we are going to apply joins on these tables and see the data results.

Inner Joins

An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. 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. Actual SQL implementations will normally use other approaches where possible, since computing the Cartesian product is not very efficient. This type of join occurs most commonly in applications, and represents the default join-type.

Example: This is explicit inner join:

Use VendorGOSELECT v.VendorId, v.VendorFName, v.VendorLName, a.royality, a.advanceFROM dbo.Vendor as v INNER JOIN advance as a ON v.VendorId = a.VendorIdWHERE v.VendorId <= 5GO

Output:

Figure 3.

Example: This is implicit inner join:

Use VendorGOSELECT * FROM Vendor, advance WHERE Vendor.VendorId = advance.VendorId AND Vendor.VendorId <= 5GO

Output:

Figure 4.

Type of inner joins

1. Equi-Join

An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join. The query shown above has already provided an example of an equi-join:

Example:Use VendorGOSELECT * FROM Vendor INNER JOIN advance ON Vendor.VendorId = advance.VendorId GO

2. Natural Join

A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.

Example:

Use VendorGOSELECT * FROM Vendor NATURAL JOIN advanceGO

3. Cross Join

A cross join, Cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the Cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or join-condition is absent in statement.

Example:

Use VendorGOSELECT * FROM Vendor CROSS JOIN advanceGO

Use VendorGOSELECT * FROM Vendor, advanceGO

Outer Joins

An outer join retrieves all rows that satisfy the join condition plus unmatched rows in one or both tables. In most cases you use the equal operator to retrieve rows with matching columns. However you can also use any of the other comparison operators. When row with

unmatched columns is retrieved any columns from the other table that are included in the result are given null values.

Note1: The OUTER keyword is optional and typically omitted

Note2: You can also code left outer joins and right outer joins using the implicit syntax.

Three types of outer joins.

1. Left Outer Join

The result of a left outer join (or simply left join) for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).

Example:

Use VendorGOSELECT VendorFName, Vendor.VendorId, VendorLName, Advance FROM Vendor LEFT JOIN advance ON Vendor.VendorId = advance.VendorIdGO

Output:

Figure 5.

2. Right Outer Join

A right outer join (or right join) closely resembles a left outer join, except with the tables reversed. Every record from the "right" table (B) will appear in the joined table at least once.

If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in A.

Example :

Use VendorGOSELECT VendorFName, advance.VendorId, VendorLName, Advance FROM Vendor RIGHT JOIN advance ON Vendor.VendorId = advance.VendorIdGO

3. Full outer join

A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

Example:

Use VendorGOSELECT * FROM Vendor FULL OUTER JOIN advance ON Vendor.VendorId = advance.VendorIdGO

OUTPUT:

Figure 6.