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
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!
