SQL Joins

Some of users always waver to “which join clause should i use in my sql queries?” so let’s see this issue with samples…

first we are creating 2 cursor for testing

CREATE CURSOR Cursor1 (id int, history c(20))
INSERT INTO Cursor1 values (1,’Cursor1 with ID 1′)
INSERT INTO Cursor1 values ( 2,’Cursor1 with ID 2′)
INSERT INTO Cursor1 values ( 3,’Cursor1 with ID 3′)
INSERT INTO Cursor1 values ( 4,’Cursor1 with ID 4′)

CREATE CURSOR Cursor2 ( id int, history c ( 20))
INSERT INTO Cursor2 values ( 1,’Cursor2 with ID 1′)
INSERT INTO Cursor2 values ( 2,’Cursor2 with ID 2′)
INSERT INTO Cursor2 values ( 5,’Cursor2 with ID 5′)
INSERT INTO Cursor2 values ( 6,’Cursor2 with ID 6′)

1st sample for inner join and the result

SELECT ;
cursor1.id id1,cursor1.history history1,cursor2.id id2,cursor2.history history2 ;
FROM cursor1 ;
INNER JOIN cursor2 ON cursor1.id=cursor2.id INTO CURSOR innerjoin

2nd sample for left join and the result

SELECT ;
cursor1.id id1,cursor1.history history1,cursor2.id id2,cursor2.history history2 ;
FROM cursor1 ;
LEFT JOIN cursor2 ON cursor1.id=cursor2.id INTO CURSOR leftjoin

3st sample for right join and the result

SELECT ;
cursor1.id id1,cursor1.history history1,cursor2.id id2,cursor2.history history2 ;
FROM cursor1 ;
RIGHT JOIN cursor2 ON cursor1.id=cursor2.id INTO CURSOR rightjoin

4th sample for full join and the result

SELECT ;
cursor1.id id1,cursor1.history history1,cursor2.id id2,cursor2.history history2 ;
FROM cursor1 ;
FULL JOIN cursor2 ON cursor1.id=cursor2.id INTO CURSOR fulljoin

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s