CODEWITHSUNDEEP
mysqlsql
neeewby
neeewby

Reputation: 33

SQL Select from two tables, get null values for columns if WHERE clause is false

http://sqlfiddle.com/#!9/cfd41ef/1

I want to get every row from table person and if the clause WHERE is false I want to get null values. Is there a way to do this? Thanks in advance.

CREATE TABLE `pet` (
    `owner_id` INT(11) NULL DEFAULT NULL,
    `pet_type` ENUM('DOG','CAT') NULL DEFAULT NULL,
    `pet_name` VARCHAR(50) NULL DEFAULT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

CREATE TABLE `person` (
    `id` INT(11) NULL DEFAULT NULL,
    `first_name` VARCHAR(50) NULL DEFAULT NULL,
    `last_name` VARCHAR(50) NULL DEFAULT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

INSERT INTO `person` (`id`, `first_name`, `last_name`) VALUES
    (1, 'qwe', 'asd'),
    (2, 'asd', 'fgh'),
    (3, 'zxc', 'vbn');

INSERT INTO `pet` (`owner_id`, `pet_type`, `pet_name`) VALUES
    (1, 'DOG', 'rex');

SELECT person.*, pet.pet_name FROM person LEFT JOIN pet ON person.id = pet.owner_id WHERE pet.pet_type = 'DOG'

Thank you Lukasz Szozda and Yogesh Sharma for your answer moving the condition to ON does what I wanted

Upvotes: 3

Views: 85

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Move to ON clause instead of going with where clause :

SELECT person.*, pet.pet_name 
FROM person LEFT JOIN 
     pet 
     ON person.id = pet.owner_id and pet.pet_type = 'DOG';

You where clause turns into inner join which is the problem.

Upvotes: 4

Lukasz Szozda
Lukasz Szozda

Reputation: 175954

You could move condition to ON:

SELECT person.*, pet.pet_name 
FROM person 
LEFT JOIN pet 
  ON person.id = pet.owner_id
 AND pet.pet_type = 'DOG';

SQLFiddle Demo

Upvotes: 4

Related Questions