MySQL Tips and Tricks – Part1

MySQL Tips and Tricks – Part1


MySQL like any other database has its own pros and cons, you can find them on the web easily, here are a couple Pros & Cons of MySQL Blob'd Images or Pros and Cons of MySQL

In this blog I will talk about INNER JOIN vs. IN and LEFT JOIN vs. NOT IN, you can also swap this logic with IN vs. Exists and NOT IN vs. Not Exists

As I read and used extensively those different approaches, I could not tell a clear approach on when to use LEFT JOIN vs. NOT IN, or INNER JOIN vs. IN.

MySQL Gurus say you have to try both approaches and see which one performs better, and that's basically what I do.

Here is an example

Given this table

'Employee' (id, username, firstname, lastname)

, and table

'Project' (id, name, startdate, duedate)

, and the bridge table

'AssignedProjects' (employee_id, project_id)

.

To get all employees who are assigned to projects, here are the two approaches

SELECT username, firstname, lastname
FROM Employee
WHERE id IN (SELECT ap.employee_id
  FROM AssignedProjects ap);

or

SELECT e.username, e.firstname, e.lastname
FROM Employee e
INNER JOIN AssignedProjects ap
 ON ap.employee_id = e.id;

To get all employees who are not assigned to projects, here are the two approaches

SELECT username, firstname, lastname
FROM Employee
WHERE id NOT IN (SELECT ap.employee_id
  FROM AssignedProjects ap);

or

SELECT e.username, e.firstname, e.lastname
FROM Employee e
LEFT  JOIN AssignedProjects ap
 ON ap.employee_id = e.id
WHERE e.id IS NULL;  # this will filter the requested list

Good luck 🙂

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *