CODEWITHSUNDEEP
sqloracle-databaseexists
Cold.Berg
Cold.Berg

Reputation: 13

How EXISTS works in the oracle SQL?

Beginner of Oracle SQL, I have some confusions about EXISTS. Here is an example:

 SELECT PORT_ID
 FROM PORTS P1
 WHERE EXISTS 
 (SELECT * FROM SHIPS S1
 WHERE P1.PORT_ID = S1.HOME_PORT_ID);

Let's say there are some rows that shares the same value in columna PORT_ID and HOME_PORT_ID, which means the subquery in the WHERE clause return TRUE.

I am wondering which rows will be selected. Do all PORT_ID in the table PORTS will be selected or just PORT_ID that are equal to HOME_PORT_ID will be selected?

Upvotes: 1

Views: 223

Answers (2)

Bohemian
Bohemian

Reputation: 425013

All PORT_ID values from PORTS whose values exist in the HOME_PORT_ID column of the SHIPS table are returned.

Your query, which queries SHIPS once for every row in PORTS, can be rewritten more efficiently as:

SELECT PORT_ID
FROM PORTS
WHERE PORT_ID IN
 (SELECT HOME_PORT_ID FROM SHIPS)

which only queries SHIPS once. But it can be rewritten even more efficiently as:

SELECT DISTINCT PORT_ID
FROM PORTS
JOIN SHIPS ON PORT_ID = HOME_PORT_ID

which will can efficiently use the index on HOME_PORT_ID if one exists, or if not do this:

SELECT DISTINCT HOME_PORT_ID
FROM SHIPS
JOIN PORTS ON PORT_ID = HOME_PORT_ID

which will use the primary key index of PORT (which will exist)

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 156978

Only the rows in ports that have a matching line in ships (using predicate P1.PORT_ID = S1.HOME_PORT_ID) will show up.

That means that if there is no row in ships with the same port_id, the rows of ports will not be shown.

Upvotes: 1

Related Questions