Transform SQL-Query to not use subqueries

Applications Programming
1

Hi,

I have a working SQL-Query here, in which I'm using subqueries to get the needed result.

The idea behind this, is to get a list of networks which have 2 tags set in the database. Because of the way it is stored, i get a tuple for each Tag, so I get a duplicate of all networks.

The Tables and the query:

Table: IPv4Network
  id,ip,name,comment

Table: TagStorage(network_id -> tag_id like 1 -> n)
  tag_id, network_id 

Table: TagTree
  id, tag

select ip,mask,name,tag
from IPv4Network 
inner join TagStorage         on     IPv4Network.id = network_id 
inner join TagTree         on         tag_id = TagTree.id 
where 
   ip in (select ip from IPv4Network 
        inner join TagStorage on IPv4Network.id = network_id 
        inner join TagTree on tag_id = TagTree.id where tag="AUTO_PROVISIONING") 
    and ip in (select ip from IPv4Network 
           inner join TagStorage on IPv4Network.id = network_id
           inner join TagTree on tag_id = TagTree.id where tag="SALES")
group by ip;

The result without the subqueries:

+------------+------+---------+-------------------+
| ip         | mask | name    | tag               |
+------------+------+---------+-------------------+
| 1111111512 |   26 | AUTO_01 | AUTO_PROVISIONING |
| 1111111576 |   26 | AUTO_02 | AUTO_PROVISIONING |
| 1111111640 |   26 | AUTO_03 | AUTO_PROVISIONING |
| 1111111704 |   26 | AUTO_04 | AUTO_PROVISIONING |
| 1111111512 |   26 | AUTO_01 | SALES             |      
| 1111111576 |   26 | AUTO_02 | SALES             |      
| 1111111640 |   26 | AUTO_03 | SALES             |      
| 1111111704 |   26 | AUTO_04 | SALES             |      
+------------+------+----------------+------------+

Subqueries are evil in terms of performance. The last time I worked with this, I wrapped it in some lines of code and sorted out the duplicates.

I'm wondering if there's not a more clever alternative in plain SQL, which is not such a performance killer than the subqueries.

Thanks in advance.

2

It is difficult to reproduce your test case because it is not clear as to how your data is stored in your tables.
Can you show the data in those three tables? (Maybe only for the 4 IP values in your output.)

You've shown the output without subqueries.

(a) Is the output incorrect? There do not appear to be any duplicate records in the output. The IP "1111111512" is repeated, sure, but the first record is for tag "AUTO_PROVISIONING" and the second for tag "SALES". So each tuple, as a whole, is unique. A case of duplicate records would be where you have this entire tuple:

1111111512 - 26 - AUTO_01 - AUTO_PROVISIONING

occurring more than once.

(b) Is your output *with* subqueries any different? What does your output look like with subqueries? Is that as per your expectation (but performing poorly)?