INNER JOIN between 2 tables vs WHERE on one table performance

Question

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?

1. Query with INNER JOIN

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

2. Query only with WHERE

   SELECT doctors.* FROM doctors WHERE doctors.clinic_id = 1;

Answer 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.