We have 2 tables in a database. The tables are:
As you can see, Customers has Cars as a foreign key. It means that every customer has an ID number, a Name and a Car ID that points to the corresponding car in the Cars table. You can see the tables’ structure in the following picture:
Every car has a Car ID, a Brand and a Price.
Ok, now lets make a query. What we want is: Get a list of all customers who has an Audi car. Using joins, the query would be:
SELECT Name From Customers Inner Join Cars ON Customers.Cars = Cars.CarID
WHERE Cars.Brand like 'Audi';
Honestly, I don’t like to use joins myself. It would be difficult to explain this query to a person who doesn’t know anything about SQL. An alternative comes from the following pseudocode:
Use the Testools database;
Get the names from the Customers table
Where the Cars equals to (get the Car Id from the Cars table Where the Brand equals to Audi);
That is more understandable for anyone. You just put the result from the query get the Car Id from the Cars table Where the Brand equals to Audi into Cars, since you know that the query will return an ID for the Audi brand, and that Cars stores IDs for the cars. Translated to SQL, the query is:
SELECT Name From Customers
WHERE Cars IN (SELECT CarID from Cars where Brand like 'Audi');
As you can see, the result is the same. The query is just more understandable and simple. Think about the “IN” like “EQUALS”.