Specifying Join Criteria in a Natural Join

Since 9i, the Natural Join feature joins tables on all columns of the same name. You don't need to specify join criteria in the WHERE clause. In the following example, both tables have keys named 'PK':

DROP TABLE MASTER;
CREATE TABLE MASTER (pk NUMBER(10), mstr VARCHAR2(100));
-- primary key only needed to ENFORCE data integrity
ALTER TABLE MASTER ADD PRIMARY KEY (pk);

DROP TABLE slave;
CREATE TABLE slave (pk NUMBER(10), sstr VARCHAR2(50));

-- primary key only needed to ENFORCE data integrity
ALTER TABLE slave ADD primary KEY (pk);
-- foreign only needed to ENFORCE referential integrity
--ALTER TABLE slave add foreign key (pk) REFERENCES MASTER (pk);
--ALTER TABLE slave DROP constraint SYS_C002722;

INSERT INTO MASTER VALUES (1, 'M1');
INSERT INTO MASTER VALUES (2, 'M2');
INSERT INTO MASTER VALUES (3, 'M3');

INSERT INTO SLAVE VALUES (1,'S1-1');
INSERT INTO SLAVE VALUES (2,'S2-2');
INSERT INTO SLAVE VALUES (3,'S3-3');

COMMIT;

SELECT *
FROM MASTER NATURAL JOIN SLAVE
--WHERE slave.sstr LIKE 'S2%';

Note we did NOT get a Cartesian product -- only those rows matching.

If I had named the string columns the same in both tables, the SQL engine would have tried to match rows by that column as well.