Suppose you have one table that has names and phone numbers of people you have contacted and another table which contains names and phone numbers of your contacts.
Phone Calls Made – Table Name: Callsmade
Contact List – Table Name: Contacts
Now you want to find those records that you have made calls to that are not in your contact list AND those records that you have never made a phone call to but are in your contact list. I know you can do something like this.
--Gets the Calls made but not in Contacts SELECT * FROM Callsmade WHERE NOT EXISTS (SELECT * FROM Contacts WHERE Contacts.Phone = Callsmade.phone) UNION ALL --Gets the Contacts but no calls made SELECT * FROM Contacts WHERE NOT EXISTS (SELECT * FROM Callsmade WHERE Callsmade.Phone = Contacts.phone)
But this seems a little confusing and inefficient. Instead you can use something like this.
Select fnl.callsmade, fnl.contactlist, tmp.Phone from ( Select max(tmp.callsmade) as callsmade, max(tmp.contactlist) as contactlist ,tmp.Phone from (Select ' ' as callsmade, Names as contactlist, Phone as Phone from Contacts Union ALL Select Name as callsmade, ' ' as contactlist, Phone as Phone from CallsMade) tmp group by tmp.Phone,tmp.callsmade, tmp.contactlist) fnl where fnl.contactlist =' ' or fnl.callsmade=' '
The result will be a list that answers the initial question. You can also change your where clause to show where they match and where the data is in one table but not the other. This query gives you a little more flexibility than using the NOT EXIST, especially if you want to look at the data in different ways.
I realize that there are times that this type of query is not appropriate but I have actually found it to work just fine in cases with less than 300K records. Once again this just proves there any many ways to answer a question especially in SQL.
If you have a different way, please share!
Looking for quality web hosting? Look no further than Arvixe Web Hosting!