CODEWITHSUNDEEP
mysqldistinct
Aymen
Aymen

Reputation: 83

How to improve MySql query that trying to find distinct values from two tables?

SELECT distinct  source_IP, timestamp FROM  tb1 , tb2 WHERE tb1.source_IP 
not in ( SELECT source_IP FROM tb2 WHERE tb1.source_IP = tb2.source_IP) 
AND tb1.timestamp not in 
( SELECT timestamp FROM tb2 WHERE tb1.timestamp = tb2.timestamp )

The above query has been written to compare attributes (source_IP and timestamp) of tb1 with the same attributes in tb2 and select only the distinct ones that belong to tb1. However, this query is working fine, but I am looking for better way to make it more efficient, since there are three queries in the statement. Any suggestions please.

Upvotes: 1

Views: 223

Answers (2)

Aymen
Aymen

Reputation: 83

Thanks God, this query is working fine:

SELECT source_IP, timestamp FROM tb1 WHERE  source_IP NOT IN (SELECT source_IP FROM tb2) 
AND timestamp NOT IN (SELECT timestamp FROM tb2)

Thanks to Roee Adler compare differences between two tables in mysql

Upvotes: 0

Arion
Arion

Reputation: 31249

You can use not exists. Like this:

SELECT DISTINCT 
   tb1.source_IP, 
   tb1.timestamp 
FROM 
   tb1 
WHERE NOT EXISTS
   (SELECT NULL FROM tb2 
    WHERE tb1.source_IP=tb2.source_IP AND tb1.timestamp=tb2.timestamp)

Upvotes: 2

Related Questions