SQL Joins | Oracle DBA Tutorial pdf

SQL Joins

Oracle now follows ANSI/ISO join syntax (similar to SQL Server). You can also use earlier versions join syntax. However, new ANSI/ISO standard syntax is more comprehensive. Here are few examples.
Say we have 2 tables – EMP (eno, ename, dno) and DEPT (dno, dname). To list all employee names and department names we can now write following query.
SELECT E.ENAME, D.DNAME FROM EMP JOIN DEPT ON (E.DNO = D.DNO)
We can use LEFT, RIGHT or FULL keywords for outer join. For example, to list all employees even when some employees may not belong to any department, we can issue this query.
SELECT E.ENAME, D.DNAME FROM EMP LEFT JOIN DEPT ON (E.DNO = D.DNO)
This is definitely more intuitive than earlier syntax with “+” operator!
Example of updating columns of table with data from another table:
CREATE TABLE INFO1 (CODE VARCHAR2(3) PRIMARY KEY,
COUNTRY VARCHAR2(100), CAPITAL VARCHAR2(100), PHONECODE NUMBER(4),
CURRENCYCODE VARCHAR2(3));
INSERT INTO INFO1 VALUES ('IND','India','New Delhi',NULL,NULL);
INSERT INTO INFO1 VALUES ('GBR','United Kingdom','London',NULL,NULL);
INSERT INTO INFO1 VALUES ('FRA','France','Paris',NULL,NULL);
INSERT INTO INFO1 VALUES ('USA','United States','Washington DC',NULL,NULL);
INSERT INTO INFO1 VALUES ('ITA','Italy','Rome',NULL,NULL);
INSERT INTO INFO1 VALUES ('SWE','Sweden','Stockholm',46,'SEK');
COMMIT;
CREATE TABLE INFO2 (CODE VARCHAR2(3), PHONECODE NUMBER(4), CURRENCYCODE
VARCHAR2(3) );
INSERT INTO INFO2 VALUES ('IND',91,'INR');
INSERT INTO INFO2 VALUES ('GBR',44,'GBP');
INSERT INTO INFO2 VALUES ('FRA',33,'EUR');
INSERT INTO INFO2 VALUES ('USA',1,'USD');
INSERT INTO INFO2 VALUES ('CAN',1,'CAD');
COMMIT;
/* case 1 – wrong */
UPDATE INFO1 i1 SET (PHONECODE,CURRENCYCODE) = ( SELECT PHONECODE,
CURRENCYCODE FROM INFO2 i2 WHERE i1.CODE = i2.CODE )
-- Sweden's data becomes null by running this!!
/* case 2 – correct */
UPDATE INFO1 i1 SET (PHONECODE,CURRENCYCODE) = ( SELECT PHONECODE,
CURRENCYCODE FROM INFO2 i2 WHERE i1.CODE = i2.CODE )
WHERE EXISTS ( SELECT 1 FROM INFO2 i22 WHERE i1.CODE = i22.CODE)
-- Sweden's existing data remains correct