INNER JOIN between 2 tables vs WHERE on one table performance Suppose I have two tables:

CREATE TABLE `doctors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `clinic_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `clinics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


The next queries returns the same data but for large tables wich is better for performance?


Query with INNER JOIN

SELECT doctors.*
  FROM doctors
 INNER JOIN clinics
    ON clinics.id = doctors.clinic_id
 WHERE clinics.id = 1;
Query only with WHERE

SELECT doctors.*
  FROM doctors
 WHERE doctors.clinic_id = 1;
The second query. But why are you comparing them? 

They may return the same data but they are answering two different questions. The first retrieves all records from doctors where a record exists in clinic with id=1. The second retrieves all records from doctors with clinic_id=1 regardless of whether a record exists in clinic. 

In the absence of a foreign key constraint between the two tables, the queries are not comparable.