You don't need all the derived tables. You are joining the basic (product
) too many times. You can write the query joining it only once.
Compound indices are a must for EAV designs. Try adding an index on (attribute_id, product_id, value)
and then the query:
SELECT t0.id,
t1.`value` AS length,
t2.`value` AS height,
t3.`value` AS family
FROM
products t0
INNER JOIN
product_eav_decimal t1
ON t1.product_id = t0.id
AND t1.attribute_id = 91
AND t1.`value` BETWEEN 15 AND 35
LEFT JOIN
product_eav_decimal t2
ON t2.product_id = t0.id
AND t2.attribute_id = 80
--
--
--
LEFT JOIN -- LEFT or INNER join
product_eav_decimal t6
ON t6.product_id = t0.id
-- AND t6.attribute_id =
ORDER BY t0.id ASC ;
Try this
select a.Date, a.Foo, b.Bar
from TableA a LEFT JOIN TableB b ON a.Date = b.Date
UNION
select b.Date, a.Foo, b.Bar
from TableB b LEFT JOIN TableA a ON a.Date = b.Date
Strictly speaking, the query above does not quite have the same semantics as the original full join, with respect to duplicates. A correct transformation of full join is to a left join union all anti-semi-join:
SELECT
TA.TheDate,
TA.Foo,
TB.Bar
FROM dbo.TableA AS TA
LEFT JOIN dbo.TableB AS TB
ON TB.TheDate = TA.TheDate
UNION ALL
SELECT
TB.TheDate,
Foo = NULL,
TB.Bar
FROM dbo.TableB AS TB
WHERE NOT EXISTS
(
SELECT NULL
FROM dbo.TableA AS TA
WHERE TA.TheDate = TB.TheDate
);
This second rewrite will also facilitate the use of an index on TheDate
columns.
Best Answer
Simulation of
FULL OUTER JOIN
:(The link given is less efficient because it gets the Intersection twice, then dedups by doing
UNION DISTINCT
.)If you need a
FULL OUTER JOIN
between more than 2 tables, then it gets messier. (But you did not ask for that.)