Saturday, February 2, 2013

How to avoid the use of joins in SQL: Where in

I'm not saying that using joins for SQL queries is bad, not at all. But I met a lot of people that don't like them and prefer to use some alternatives. Here is a solution to avoid the use of joins. Just in case, some people think that this method is not a good habit, but it's up to you if you use it or no.
We have 2 tables in a database. The tables are:
  • Customers
  • Cars
In this case, I’m using MySQL, but, of course, this works with any SQL engine.  The following picture shows the Testools database, with two tables: Customers and Cars. Imagine that this is the database of a car’s store named Testools.image
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:

USE Testools;
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:

Use Testools;
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”.

0 comentarios:

Post a Comment