>> JOINS where no rows are defined. Then the 0 or -1 value need to be
>> a computed value. Won't this throw off index lookups? (I might be
>> more confused in this area).
>
>Not following this.
The nulls are generated by something like this
SELECT c.circuit_id,
cc.customer_id
FROM circuit AS c
LEFT JOIN circuit_customer AS cc
ON c.circuit_id = cc.circuit_id
To make a magic '0' customer we would be required to use
COALESCE(cc.customer_id, '0')
I dont think the optimizer will do anything clever with the '0' we have
computed from null.
I could ofc. by default assign all unassigned circuits to '0' in
circuit_customer. I'm not a fan though.
>BTW, if you want a fast plan over the current
>data without consideration of aesthetics, try this:
>
>CREATE VIEW view_circuit_with_status AS (
> SELECT r.*,
> s.circuit_status,
> s.customer_id AS s_customer_id,
> p.line_speed,
> p.customer_id AS p_customer_id
> FROM view_circuit r
> JOIN view_circuit_product_main s
> ON r.circuit_id = s.circuit_id
> AND r.customer_id, s.customer_id
> JOIN view_circuit_product p
> ON r.circuit_id = p.circuit_id
> AND r.customer_id, s.customer_id
> UNION ALL SELECT r.*,
> s.circuit_status,
> s.customer_id AS s_customer_id,
> p.line_speed,
> p.customer_id AS p_customer_id
> FROM view_circuit r
> JOIN view_circuit_product_main s
> ON r.circuit_id = s.circuit_id
> AND r.customer_id IS NULL
> AND s.customer_id IS NULL
> JOIN view_circuit_product p
> ON r.circuit_id = p.circuit_id>
I will have to figure something out, but this specific case is still problematic
since we would like to filter this view using different criteria's, like circuit_no,
products or customers.
But with all these detours, I assume that a change to IS NOT DISTINCT FROM,
is difficult or not wanted?
