SQL joins graphically simplified...

SQL[structured query language] is the language to get data from a DB. It let you retrieve data from a simple "select *" to many customized ways. "JOIN" is the keyword to retrieve data from multiple tables with a given relationship "ON".

There are few more keywords used with JOIN, and some are optional. ex: INNER, OUTER,LEFT,RIGHT,FULL,CROSS,... those keywords depends on the DB you are using.

As given early JOIN give us the opportunity to retrieve rows which are related on two tables. So those could be able to show in a Cartesian Venn diagram. So i have created two sample tables and added sample data to those. lets see how this can be simulated.

First lets create sample tables:


CREATE TABLE tblleft ( id_customer int(10), customer_name varchar(10) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into tblleft (id_customer, customer_name) values (1, 'amara');
insert into tblleft (id_customer, customer_name) values (2, 'dasun');
insert into tblleft (id_customer, customer_name) values (3, 'saman');
insert into tblleft (id_customer, customer_name) values (4, 'nimal');
insert into tblleft (id_customer, customer_name) values (5, 'kasun');

CREATE TABLE tblright ( id_order int NOT NULL AUTO_INCREMENT, id_customer int, equipment varchar(10) DEFAULT '_', PRIMARY KEY (id_order) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into tblright (id_order, id_customer, equipment) values (1, 1, 'food');

insert into tblright (id_order, id_customer, equipment) values (2, 2, 'car');

insert into tblright (id_order, id_customer, equipment) values (3, 6, 'computer');

insert into tblright (id_order, id_customer, equipment) values (4, 7, 'fan');

insert into tblright (id_order, id_customer, equipment) values (5, 3, 'mobile');

1:

First lets do the simple join and retrieve data which are common with id_customer in the two tables 

SELECT

    *

FROM

    tblleft l

JOIN tblright r

ON

    l.id_customer=r.id_customer;

hers is the results:

2:

SELECT

    *

FROM

    tblleft l

LEFT OUTER JOIN tblright r

ON

    l.id_customer=r.id_customer;

3:

SELECT

    *

FROM

    tblleft l

RIGHT OUTER JOIN tblright r

ON

    l.id_customer=r.id_customer;

4:

SELECT

    *

FROM

    tblleft l

LEFT OUTER JOIN tblright r

ON

    l.id_customer=r.id_customer

WHERE

    r.id_order IS NULL;

5:

SELECT

    *

FROM

    tblleft l

RIGHT OUTER JOIN tblright r

ON

    l.id_customer=r.id_customer

WHERE

    l.id_customer IS NULL;

6:

SELECT

    *

FROM

    tblleft l

LEFT OUTER JOIN tblright r

ON

    l.id_customer=r.id_customer

UNION

SELECT

    *

FROM

    tblleft l

RIGHT OUTER JOIN tblright r

ON

    l.id_customer=r.id_customer;

7:

SELECT

    *

FROM

    tblleft l

LEFT OUTER JOIN tblright r

ON

    l.id_customer=r.id_customer

WHERE

    r.id_order IS NULL

UNION

SELECT

    *

FROM

    tblleft l

RIGHT OUTER JOIN tblright r

ON

    l.id_customer=r.id_customer

WHERE

    l.id_customer IS NULL;

8:Cross join will provide the Cartesian product , but it cant be drawn by a Venn diagram.

SELECT

    *

FROM

    tblleft l

JOIN tblright r;

or 

SELECT

    *

FROM

    tblleft l ,

    tblright r;

I hope you get some idea on how those result sets are described with Venn diagrams.

Thank you!