Svinjica Svinjica - 6 months ago 10
SQL Question

Can't see NULL values in created VIEW using MySQL

When creating view in MySQL I can't see rows with NULL values in created view.

Here we can see table

mail_prijava

enter image description here

and table
status


enter image description here

So, I wanted to create VIEW from table
mail_prijava
and table
status
using SQL code below

CREATE OR REPLACE VIEW
v_mail_prijava_test
AS
SELECT
mail_prijava.id,
mail_prijava.naselje AS naselje,
mail_prijava.ulica AS ulica,
mail_prijava.email AS email,
mail_prijava.napomena AS napomena,
IFNULL(status.opis AS "test") AS status
DATE_FORMAT(datum_unosa, "%d.%m.%Y.") AS datum_servisa,
vrijeme_unosa
FROM mail_prijava
INNER JOIN status ON status.id = mail_prijava.status_id


When using code above I get this table:
enter image description here

Problem is that row with null value from table
mail_prijava
is not showing and I'm wondering why is that and is it possible to show rows with NULL in created view.

Thank you

jpw jpw
Answer

To get all rows, including those that don't have any matches in the status table you need to use a left join instead of the inner join.

Change this:

INNER JOIN status ON status.id = mail_prijava.status_id

To this:

LEFT JOIN status ON status.id = mail_prijava.status_id

Also, your IFNULL line looks odd, you probably want:

IFNULL(status.opis, "test") AS status -- this will show "test" as status if it's null
Comments