CODEWITHSUNDEEP
mysqlsql
Thallius
Thallius

Reputation: 2619

Does a inner join perform as fast as a where clause?

Lets assume I have two tables

create table a 
(
    id int primary key auto_increment,
    b_id int,
    Index bid (b_id)
);

create table b
(
    id int primary key auto_increment
    name varchar(100)
);

insert into a values (1, 1), (2, null), (3, 2), (4, null), (5, 3);
insert into b values (1, 'a'),(2, 'b'),(3, 'c');

And I need all rows from table a which have a value in row 2. so I can simple do an inner join

select * 
from a
inner join b on a.b_id = b.Id

In real I have about 5 tables i need to join in a query. So my question is:

Would it be faster to add a where clause to avoid searching for rows where b_id is null or is this not needed?

select *
from a
inner join b on a.b_id = b.id
where a.b_id is not null

Upvotes: 1

Views: 208

Answers (1)

Nathan Hughes
Nathan Hughes

Reputation: 96454

The where clause excluding nulls is not needed.

Don’t worry about nulls getting returned by the join matching null ids; null isn’t equal to itself, so the inner join won’t match any nulls in one table to nulls in another.

Use joins for relating tables. Use where for filtering. There is some overlap in what you can do with these but it’s a style issue. Performance gains come from making sure your code isn’t doing more work than it needs to (such as by making sure it makes use of the indexes).

Also be aware that columns that are part of a primary key can’t be null.

Upvotes: 2

Related Questions