Another way to do a not exists SQL query with best performance

This comes up again and again as a thorny and persistent problem. I got this new way of doing this from my friend.

SELECT alias1.PKCol1, alias1.Col2
FROM table1 alias1
WHERE alias1.Col3= 11 AND alias1.Col4= 43 AND
      NOT EXISTS (SELECT 'x' FROM table2 alias2 WHERE alias2.PKCol1 = alias1.PKCol1)

Comments

Post a Comment

Popular posts from this blog

Tutorial: Using Google Cloud Storage from C# and .NET

There is almost no samples or documentation available anywhere for this, so I thought I would write this up myself as using the storage from GCS (similar to Amazon S3 and Azure Blob Storage) is one of the most basic things which people do with the cloud. The very first thing to do after creating the C# project in Visual Studio is to install the Nuget package mentioned here: https://www.nuget.org/packages/Google.Apis.Storage.v1/ There is a lot of confusion with multiple packages available, but this is what you want for GCS. Install-Package Google.Apis.Storage.v1 You are going to need to reference the following namespaces: using Google.Apis.Auth.OAuth2; using Google.Apis.Storage.v1; Setup the basic items you need for making the calls: private static ClientSecrets _clientSecrets = new ClientSecrets();         private static UserCredential _userCredential = null;         private static StorageService _storageService = new StorageSer...
Read more

Late 2008 Macbook only giving 1.5 gb/s speed with 6 gb/s Intel SSD?

You may find it online that this is due to the nvidia chipset used in the macbook. There is apparently a bug which causes all Intel SSDs to run at 1.5 gb/s with this chipset. What is interesting is that I have an old HP ML115 server in the basement which was also showing the speed of the Intel SSD as 1.5 gb/s whereas another SSD from PNY was able to do 3 gb/s which is what the motherboard actually supports. Guess what? That server motherboard also uses an nvidia chipset. I spent the whole day tinkering with the server trying to figure out why my expensive Intel SSD is giving me 1.5 gb/s while the cheap PNY was giving me 3 gb/s. No matter what I did, it did not work - including connecting the Intel SSD to a RAID controller card and using it without RAID. Some people have given the workaround to connect the hard drive to the CD drive connector which uses the Intel chip and not nvidia one. What I did is to put the PNY SSD from my server in the macbook. It consistently gave 3 gb/s ...
Read more

The ridiculous interview experience

I have encountered a few interview questions which I feel strongly enough to talk about.... 1) List all the scenarios where a stack overflow exception can occur . 2) Difference between mutex and lock ? These two questions seemingly different are in the same category - I work on shitty code either I wrote or I inherited from someone else - tell me whether you know about what happens in shitty code. The problem is that if you are a great programmer and you write great code, you may have never have encountered such scenarios before - your knowing or not knowing about this in no way indicates how good you are. I joined the job where they asked the mutex question and rewrote the entire code - their implementation was totally wrong and how they were trying to solve the problem was also totally wrong. Mutex was not the way to go. You are not a less capable resource because your code and design is so good that they did not have the flaws which you needed to know about to succeed in this...
Read more