Transact SQL Join in Query Analyzer

Tuesday, November 16, 2010 by: Andy Kurnia Prayoga Made

One of the Transact SQL server 2000 which is very useful to us is the Transact join. It aims to combine several tables that have a primary key that refers to another table. There are 4 types of Transact join the most commonly used. Maybe many people are confused about the difference between inner join, left join, right joins and full join on writing Transact SQL server in query analyzer. To eliminate confusion, please try for yourself Transact-Transact below in your query analyzer. Please try and find their own conclusions.

create database andy

use andy

create table pegawai
(
noID int,
nama varchar(20),
tgllahir varchar(10),
alamat varchar(30),
kodepegawai varchar(5)
)

insert into pegawai values(1,'Munson','25-12-1989','denpasar 20','DR')
insert into pegawai values(2,'Steven','14-02-1989','gianyar 31','MGM')
insert into pegawai values(3,'Edward','13-11-1988','tabanan 25','CS')
insert into pegawai values(4,'Paul','12-06-1990','amlapura 16','MGP')
insert into pegawai values(5,'Gibson','07-07-1989','badung 11','CS')

select * from pegawai

create table job
(
kodepegawai varchar(5),
bidangpekerjaan varchar(20),
gaji varchar(20)
)

insert into job values('DR','direktur','Rp 5.000.000')
insert into job values('MGP','manager personalia','Rp 2.500.000')
insert into job values('MGM','manager marketing','Rp 3.000.000')
insert into job values('MGPD','manager produksi','Rp 3.200.000')
insert into job values('KR','karyawan','Rp 1.100.000')
insert into job values('CS','cleaning service','Rp 800.000')

select * from job


SELECT job.kodepegawai,job.bidangpekerjaan,job.gaji,pegawai.noID,pegawai.nama 
FROM job
INNER JOIN pegawai
ON pegawai.kodepegawai=job.kodepegawai
ORDER BY pegawai.noID

SELECT job.kodepegawai,job.bidangpekerjaan,job.gaji,pegawai.noID,pegawai.nama 
FROM job
LEFT JOIN pegawai
ON pegawai.kodepegawai=job.kodepegawai
ORDER BY pegawai.noID

SELECT job.kodepegawai,job.bidangpekerjaan,job.gaji,pegawai.noID,pegawai.nama 
FROM job
RIGHT JOIN pegawai
ON pegawai.kodepegawai=job.kodepegawai
ORDER BY pegawai.noID

SELECT job.kodepegawai,job.bidangpekerjaan,job.gaji,pegawai.noID,pegawai.nama 
FROM job
FULL JOIN pegawai
ON pegawai.kodepegawai=job.kodepegawai
ORDER BY pegawai.noID