CODEWITHSUNDEEP
mysqlmariadb
Peter
Peter

Reputation: 1348

Alias for using SELECT result in WHERE IN

I have a query on which I use a WHERE IN SELECT. Here is the query:

SELECT tville_id, tville_nom, tville_etat
FROM TAB_ZONE   
INNER JOIN TAB_VILLES ON tville_id = tzone_cidade
WHERE tzone_unidade1 = 1 
   OR tzone_unidade2 = 1 
   OR tzone_unidade3 = 1 
   OR tzone_unidade1 IN (SELECT tunidade_id FROM TAB_UNIDADE WHERE tunidade_parent = 1)
   OR tzone_unidade2 IN (SELECT tunidade_id FROM TAB_UNIDADE WHERE tunidade_parent = 1)
   OR tzone_unidade3 IN (SELECT tunidade_id FROM TAB_UNIDADE WHERE tunidade_parent = 1)
ORDER BY tville_etat, tville_nom

It works, but as you can see I have three time the "SELECT tunidade_id" statement in the WHERE part. Is there a way to use the result of this SELECT as an alias, so perform it only one time? (and is it usefull?)

Thanks

Upvotes: 1

Views: 77

Answers (4)

noonex
noonex

Reputation: 2075

I haven't verified, but in my understanding something like this should work:

SELECT tville_id, tville_nom, tville_etat
FROM TAB_ZONE   
INNER JOIN TAB_VILLES ON tville_id = tzone_cidade
JOIN (SELECT tunidade_id FROM TAB_UNIDADE WHERE tunidade_parent = 1 UNION SELECT 1) x on x.tunidade_id in (tzone_unidade1, tzone_unidade2, tzone_unidade3)
ORDER BY tville_etat, tville_nom

Upvotes: 1

Rick James
Rick James

Reputation: 142453

Better would be to not splay an array across columns. Instead have multiple rows in another table. With such, you can avoid the repeated query, CTEs, performance issues, etc.

Upvotes: 0

elenst
elenst

Reputation: 3987

In MariaDB 10.2 you can use common-table expressions for this purpose.

WITH cte_tunidade AS (SELECT tunidade_id FROM TAB_UNIDADE WHERE tunidade_parent = 1)
SELECT tville_id, tville_nom, tville_etat
FROM TAB_ZONE   
INNER JOIN TAB_VILLES ON tville_id = tzone_cidade
WHERE tzone_unidade1 = 1 
   OR tzone_unidade2 = 1 
   OR tzone_unidade3 = 1 
   OR tzone_unidade1 IN (SELECT * FROM cte_tunidade)
   OR tzone_unidade2 IN (SELECT * FROM cte_tunidade)
   OR tzone_unidade3 IN (SELECT * FROM cte_tunidade)
ORDER BY tville_etat, tville_nom

Whether it will bring you a noticeable performance improvement -- I guess the best way to find out is to try; but at the very least a query is better maintainable this way.

Upvotes: 2

Rahul
Rahul

Reputation: 77906

If not wrong the below conditions can be merged to one

OR tzone_unidade1 IN (SELECT tunidade_id FROM TAB_UNIDADE WHERE tunidade_parent = 1)
OR tzone_unidade2 IN (SELECT tunidade_id FROM TAB_UNIDADE WHERE tunidade_parent = 1)
OR tzone_unidade3 IN (SELECT tunidade_id FROM TAB_UNIDADE WHERE tunidade_parent = 1)

To something like

OR (tzone_unidade1,tzone_unidade2,tzone_unidade3) IN (SELECT tunidade_id FROM TAB_UNIDADE WHERE tunidade_parent = 1)

Upvotes: 0

Related Questions