BY USING JOINS, YOU CAN RETRIEVE DATA FROM TWO OR MORE TABLES BASED ON LOGICAL RELATIONSHIPS BETWEEN THE TABLES. JOINS INDICATE HOW MICROSOFT® SQL SERVER⢠SHOULD USE DATA FROM ONE TABLE TO SELECT THE ROWS IN ANOTHER TABLE.
A JOIN CONDITION DEFINES THE WAY TWO TABLES ARE RELATED IN A QUERY BY:
SPECIFYING THE COLUMN FROM EACH TABLE TO BE USED FOR THE JOIN. A TYPICAL JOIN CONDITION SPECIFIES A FOREIGN KEY FROM ONE TABLE AND ITS ASSOCIATED KEY IN THE OTHER TABLE.
SPECIFYING A LOGICAL OPERATOR (=, <>, AND SO ON) TO BE USED IN COMPARING VALUES FROM THE COLUMNS.
INNER JOIN
THIS INNER JOIN IS KNOWN AS AN EQUI-JOIN. IT RETURNS ALL THE COLUMNS IN BOTH TABLES, AND RETURNS ONLY THE ROWS FOR WHICH THERE IS AN EQUAL VALUE IN THE JOIN COLUMN.
TYPES
EQUI JOINS
NATURAL JOINS
EXAMPLE
CREATE TABLE PUBLISHERS(PUBID NUMERIC(3),BOOKNAME CHAR(25),YR NUMERIC(4),AUTHOR CHAR(25))
CREATE TABLE SALE(PUBID NUMERIC(3),BOOKTITLE CHAR(25),QTY NUMERIC(3))
CREATE TABLE BOOKS(PUBID NUMERIC(3),PUBNAME CHAR(25),TITLE CHAR(25),RATE NUMERIC(3))
INSERT INTO PUBLISHERS VALUES(101,"SQL SERVER",2003,"VENKATRAMAN")
INSERT INTO PUBLISHERS VALUES(105,"COMPUTER ARCHITECTURE",2004,"RAJESH SHARMA")
INSERT INTO PUBLISHERS VALUES(108,"COMPUTER ARCHITECTURE",2004,"RAFIQ ASLAM")
INSERT INTO PUBLISHERS VALUES(102,"DATA STRUCTURE",2006,NULL)
INSERT INTO PUBLISHERS VALUES(107,"COMPUTER ARCHITECTURE",2005,NULL)
INSERT INTO SALE VALUES(101,"SQL SERVER",100)
INSERT INTO SALE VALUES(110,"C++ PROGRAM",200)
INSERT INTO SALE VALUES(102,"JAVA PROGRAM",300)
INSERT INTO SALE VALUES(111,"VB IN 30 DAYS",200)
INSERT INTO SALE VALUES(112,"OPERATING SYSTEM",200)
