CODEWITHSUNDEEP
sqlpostgresql
Nicholas Barry
Nicholas Barry

Reputation: 568

Can I create an alias for WHERE conditions in Postgres?

I'd like to find all the companies that have employees matching certain criteria, and return information about those employees, and also about the owners of those companies. My SQL is a bit repetitive, though, and I'm wondering if there's a way to use an alias for some of my WHERE conditions.

Consider this query:

SELECT json_agg(employee.*) employees_and_admins, company.*
    FROM company
    JOIN employee ON employee.company_id = company.id
    WHERE employee.owner IS TRUE
        -- This is where the repetitive stuff starts
        OR employee.first_name IS NULL
        OR employee.last_name IS NULL
        OR employee.date_of_birth IS NULL
        OR employee.hire_date IS NULL
        OR employee.email IS NULL
    GROUP BY company.id
    HAVING sum(CASE
        -- Note the repetition; note also we're not checking employee.owner here
        WHEN (
            employee.first_name IS NULL
            OR employee.last_name IS NULL
            OR employee.date_of_birth IS NULL
            OR employee.hire_date IS NULL
            OR employee.email IS NULL)
        THEN 1 ELSE 0 END) > 0;

Is there some way to avoid repeating all those OR conditions? It has been a bit heard to search for answers online because I keep getting information about SELECT aliases.

Upvotes: 1

Views: 77

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125414

lateral and the convenient bool_or:

select json_agg(employee.*) employees_and_admins, company.*
from
    company
    inner join
    employee on employee.company_id = company.id
    cross join lateral (
        select
            employee.first_name is null
            or employee.last_name is null
            or employee.date_of_birth is null
            or employee.hire_date is null
            or employee.email is null
            as any_null
    ) cjl
where employee.owner or any_null
group by company.id
having bool_or(any_null)

The alternative to lateral is nested queries.

Upvotes: 2

klin
klin

Reputation: 121754

You can create a function for better readability (this practically will not affect performance), e.g.

CREATE OR REPLACE FUNCTION has_a_null_value(e employee)
RETURNS boolean LANGUAGE SQL AS $$
    SELECT
        e.first_name IS NULL
        OR e.last_name IS NULL
        OR e.date_of_birth IS NULL
        OR e.hire_date IS NULL
        OR e.email IS NULL
$$;

SELECT json_agg(employee.*) employees_and_admins, company.*
    FROM company
    JOIN employee ON employee.company_id = company.id
    WHERE employee.owner OR has_a_null_value(employee)
    GROUP BY company.id
    HAVING sum(has_a_null_value(employee)::int) > 0;

Upvotes: 1

Related Questions