Friday, May 28, 2010

Understanding the EQUI JOIN.

An equi join is the same as inner join and joins tables with the help of a foreign key. However, an equi join is used to display all the columns from both the tables. The common column from all the joining tables is displayed.
    Other words, 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.

Equi join Syntax is below.
            Select [column list]
            from [left joined table]                                     
          equi join [right joined table]                               
            on [join condition] 

Query of EQUI JOIN.

CREATE TABLE customer
(custid CHAR(3) NOT NULL PRIMARY KEY,
 firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales
(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);


command in EQUI JOIN
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
join sales AS s
ON c.custid = s.custid

The result is displayed below with EQUI JOIN the both table customer and sales.

No comments:

Recent Posts