How to get list of cars with a specific ID of wheel?

I have the following code snippet :

CREATE TYPE ModeleRoue (nom VARCHAR2(10) );
CREATE TYPE ModeleMoteur (nom VARCHAR2(10) );
CREATE TYPE ModeleVoiture (nom VARCHAR2(50) );
CREATE TABLE EnsModeleVoiture OF ModeleVoiture;
CREATE TABLE EnsModeleMoteur OF ModeleMoteur;
CREATE TABLE EnsModeleRoue OF ModeleRoue;
CREATE TYPE ModeleRoue (nom VARCHAR2(10) );
CREATE TYPE ModeleMoteur (nom VARCHAR2(10) );
CREATE TYPE ModeleVoiture (nom VARCHAR2(50) );
CREATE TABLE EnsModeleVoiture OF ModeleVoiture;
CREATE TABLE EnsModeleMoteur OF ModeleMoteur;
CREATE TABLE EnsModeleRoue OF ModeleRoue;

I want to get the Voitures having number of Roue = 554 :

SELECT v.numero FROM ensvoiture v
WHERE EXISTS (SELECT * FROM v.roues r WHERE r.numero=554);

What is the difference of using the table in the EXISTS subquery directly in the WHERE clause?

SELECT v.numero FROM ensvoiture v
WHERE  v.roues.numero =554;

Answer

The right way to write your first query would be:

SELECT v.numero FROM ensvoiture v
WHERE EXISTS (SELECT * FROM v.roues r WHERE r.numero=v.numero and r.numero=554);

This query, written this way, is doubtfully usefull, because it seems that you want to filter your ensvoiture table for those numbers equal to 554. Therefore, the exists would be unnecesary and you could use just your second query.

SELECT v.* FROM ensvoiture v
WHERE  v.numero =554;

This doesn’t mean that both queries will return the same data, because if there is no any r.numero=554, your first query will return nothing. The second one will keep returning data even whether the roues table doesn’t have any numero=554.