Deploying out of hours shouldn't be a thing

Getting code into production should not be hard. Nor should it have to be scheduled for special times. I have recently been involved with three early morning database deployments in a row. On questioning this, I was told the app needed to be brought down for deployment. Fair enough, many legacy applications have this issue, but there are ways and means to avoid impact to users.

Alarm bells should already be ringing.

Scenario

You drag yourself groggily down the stairs after hitting snooze a few times and fumbling around for your robe while trying not wake up your spouse and kids. Logging in while switching between yawns and sips of coffee, hoping you don't make a cock-up while your brain is half asleep and all your other colleagues are out of reach. Then wait for the application guy to do their thing, all the while thinking... "I could be sleeping though this bit." Get the go ahead. Your heart drops when you realise you started while forgetting to check something because you're just that tired. Thankfully all is ok. Now wait again for the app to start and then the tests. Go back to bed. Great. Now you're wide awake at 5 am and lie staring at the back of your eyelids for two hours... and for what? Just to push a few buttons?  AAAAAH! 

Avoiding user impact like this has its risks. I don't think I need to point out why doing anything in a production system when your body is screaming at you to go to sleep, or when all the other experts that could support you are asleep, is a bad idea.

Productivity for the rest of the day will also be impacted. All in all it's a risky AND expensive operation.

Also, for the guys involved... it just sucks.

Automation:

Do we really want an expensive resource being paid to simply push buttons? Why the hell is a DBA doing a deployment in the first place? Automation is obviously the proverbial elephant in the room. Get it done! There are some great tools out there to assist with this, like Octopus Deploy, DbUp, SSDT, Bamboo, PowerShell and much more. Now the DBA can sleep. Maybe just be on call. But we're still doing deployments out of hours...

Deploying the application in hours:

You want to deploy during office hours, but without impacting users. Have you heard of Blue / Green deployments? You effectively have an offline and online production system. You deploy to the offline one, run all of your tests and when all is good, switch. Your offline becomes your online and users won't notice a thing. Something's not right? Fine. Just don't switch. No rollback required. This is particularly great for legacy or monolithic applications.

Another option might be rolling deployments. Better for load balanced applications and micro service platforms like Service Fabric. You deploy to one server / service at a time.

And you could probably do stuff with containers and other cool stuff, but let's face it... if you're having this problem then you're probably dealing with legacy or monolithic type apps.

Deploying the database in hours:

Dealing with data is usually harder than just the apps. But in this regard, it should actually be simpler. You just have to take care with how you develop and prepare your release. If you've taken care of the automation you're pretty much good. Just take care with destructive changes. Stagger them. If you need to delete a column, make the change in the app first. Then in a future release when the column is no longer being called, drop the column then. Make sure you've load tested your deployment in your staging environment so you know there shouldn't be any user impacting changes. Develop your code and test well, and you're good.

So it may take some work to get to normal hour deployments but it can most certainly be done, and with many other side benefits to be found.

Accessing Git (Part 3) - SSH Config with multiple GIT accounts

I was going to talk about getting better integration with VSCode. There's a good article here that explains how to use the Putty toolset (caching the keys with Pageant) for managing your SSH keys rather than with SSH-Agent. It works nicely too, but I found the following downsides:

• It's something else to install.
• You have to either 
• Manually add the key(s) to Pageant each time you need to access your repo or
• Configure Pagent to load the keys on windows startup, which means being prompted for your passphrase for each key, even if you don't need them. A nuisance when you end up with multiple repos.
• The latest version of Putty conforms to a different standard for SSH keys, so you have to convert them to use them.

When using multiple keys, I prefer a different method. Roll on the config file!!

SSH Config

With the advent of the AddKeysToAgent option since OpenSSH version 7.2 (February 2016), you now have the option to be prompted to add a key only when it's needed. That's pretty cool. This is done via an SSH config file. Check out the help page for more detail if you need to, but the below was suitable for my purposes. Namely accessing multiple GIT repos with minimal fuss.

First, you need to create the config file in your .ssh folder. The file needed is just "config" (no extension). In this file we'll put something like the following (adjust to your needs):

Host bitbucket.org
    HostName bitbucket.org
    User git
    IdentityFile ~/.ssh/id_rsa_bitbucket
    AddKeysToAgent yes
 
Host work.bitbucket.org
    HostName bitbucket.org
    User git
    IdentityFile ~/.ssh/id_rsa_work
    AddKeysToAgent yes

Host github.com
    HostName github.com
    User git
    IdentityFile ~/.ssh/id_rsa_github
    AddKeysToAgent yes

With this, I can have multiple keys, and only have the relevant one loaded when I actually need it. I have two BitBucket accounts (one for work and one personal) as well as a personal GitHub account. Let's break the above down.

Host: This is our alias. We actually change our repo URL's domain to match this. So for example, if I wanted to clone the repo git@bitbucket.org:me/repo.git which is in my work account, I would have to change the URL and run the following command:

• git clone git@work.bitbucket.org:me/repo.git

Note that we've changed the URL to include "work." Everything that sits in the Host block is the config for that domain alias. If you already have a repo cloned and you need to change the URL, run "git remote set-url."

HostName: This is where the alias URL maps to. The actual domain.

IdentityFile: The path to the private key for your chosen account. i.e. without the .pub extension.

AddKeysToAgent: The magic bit. When set to "yes" it will dynamically load the key for your Host alias when its domain is called.

For extra info on the config, check out the online docs, but that's it for me.

SSH-Agent

Great! So now our keys get added dynamically and we only get prompted for a passphrase when it's needed. But we still need an agent to be running. Remember in part two when we pasted in some bash script to our .bashrc file to both start the agent and load the keys when we started Git Bash?  Well, now we only want it to start the agent. The keys are managed by the above config file.

Let's change it to this:

env=~/.ssh/agent.env

agent_load_env () { test -f "$env" && . "$env" >| /dev/null ; }

agent_start () {
    (umask 077; ssh-agent >| "$env")
    . "$env" >| /dev/null ; }

agent_load_env

# agent_run_state: 0=agent running w/ key; 1=agent w/o key; 2= agent not running
agent_run_state=$(ssh-add -l >| /dev/null 2>&1; echo $?)

if [ ! "$SSH_AUTH_SOCK" ] || [ $agent_run_state = 2 ]; then
    agent_start
fi

unset env

Try it! 

Now when you start Git Bash, it won't prompt you for your passphrase. But check your processes and you'll see ssh-agent running. Now access one of your repos (remember you should have set the domain alias). It's at this point you'll be prompted and it will cache the key. Do the same again with one of your other repos and it will prompt you for that one. Once added to the agent it will prompt you no more, for the duration of your agent's ephemeral lifetime.

Visual Studio Code

So back to Code. It doesn't look like Code supports SSH-Agent just yet, but as I mentioned in my previous post, you can get around this by running the command "code" in Git Bash. VS Code will inherit the environment of your Bash session, which includes the SSH-Agent environment variables. This is why I was considering the Putty approach, but with the config file now allowing dynamic adds, I much prefer SSH-Agent to Pageant.

There is a way to streamline this though. I simply created a .sh file with just the word "code" in it. I then created a windows shortcut to run that file using Git for Windows. I made it prettier by adding the VS Code icon to the shortcut and used that for kicking Code off. It will even dynamically prompt you when access to a repo is needed.

The only downside being that launching Code via the context menus doesn't do it. Not perfect but close to it.

Who would have thought accessing GIT could warrant three posts, but this was part of my educational journey and was more about understanding than simply following a set of prescribed steps.

I hope this helps someone else. Let me know. Adios.

Part 1: HTTPS
Part 2: SSH

Accessing GIT (Part 2) - SSH

The alternative to connecting to your remote repository via HTTPS is, of course, SSH. This involves generating a key pair. Encryption is done locally via a private key and you provide the public key to your GIT provider for decryption. This involves a little more setup than for HTTPS but is ultimately a more secure method.

As with part 1, this post could be much shorter to just get you up and running with SSH in no time, but I went through the pain of getting to grips with all of this myself wishing there was a single place I could have got all of this information from. This is for folks who would like to understand a little more than just follow a step by step without really knowing why or what.

Creating the keys:

Find and follow the instructions for your chosen GIT repository. It will most likely involve generating your key-pair via the GIT Bash terminal using the command line ssh-keygen, which will generate the certificate files, and then pasting in the text from the new public key to your account. That's pretty much all there is to it.

For extra security, it's worth putting in a passphrase. This means that if anyone was able to get a hold of your machine or private key, they would still need a memorised key to use it. Some sites allow you to use 2FA in addition as well. You can avoid using a passphrase by just leaving it blank. This makes things simple, but for obvious reasons is not recommended.

Set your target:

In the first part on HTTPS, we cloned a repo using the HTTPS URL. We could do the same here but by selecting the SSH option from the drop-down. It should look something like:

    git@github.com:UserName/Myrepo.git

compare this to the HTTPS URL of:

    https://github.com/Username/Myrepo.git

Or if you'd rather change one of your repo's that is currently set to HTTPS to SSH, you can do this too. Just run:

    git remote set-url origin git@github.com:UserName/Myrepo.git

To check, you can list your configured remote URLs with:

    git remote -v

Just be aware; If you connect for the very first time using a more GUI based software like VSCode, you may just get an error as it won't give you this message. Best do the first connect in GIT Bash.

Try it out:

Start with a git pull. The first time you connect it will ask you if you are sure you want to connect to the server and give you the fingerprint for that server. You can check your provider's documentation to confirm you're connecting to the right fingerprint.

At the time of writing this, you can find the RSA fingerprints for BitBucket and GitHub here:

BB: https://confluence.atlassian.com/bitbucket/troubleshoot-ssh-issues-271943403.html?_ga=2.97089951.1499627191.1523138139-1040706359.1522752215

GH: https://help.github.com/articles/github-s-ssh-key-fingerprints/

If you avoided using a passphrase when setting up your key-pair, then that's it. You never have to worry about connecting again. But if you (and you should) use a passphrase for more security, it gets annoying as you're asked for that key-phrase every single time you interact with the remote repo. That's where ssh-agent comes in.

SSH-AGENT:

SSH-Agent allows in-memory storage of a key. This runs within the session that kicked it off. You can manually start the agent in your session and add your key to it as follows:

    #Start the agent:
    eval $(ssh-agent -s)

    #Add your key:
    ssh-add ~/.ssh/id_rsa

You'll be asked for your passphrase when adding the key, but after that and for the duration of your session, you will no longer be asked for it.

Exit the terminal and you'll have to do it again. Yeah, it's not great to have to do this manually every time. Fortunately, like with PowerShell profiles, you have the ability to run set of commands automatically on each session start-up. Namely the .bashrc file which you should be able to find in your user folder.

You could just copy the above into the .bashrc file but GitHub gives us a much cleaner snippet to use.

The below snippet is taken from https://help.github.com/articles/working-with-ssh-key-passphrases. Just paste it into your .bashrc file.

env=~/.ssh/agent.env

agent_load_env () { test -f "$env" && . "$env" >| /dev/null ; }

agent_start () {
    (umask 077; ssh-agent >| "$env")
    . "$env" >| /dev/null ; }

agent_load_env

# agent_run_state: 0=agent running w/ key; 1=agent w/o key; 2= agent not running
agent_run_state=$(ssh-add -l >| /dev/null 2>&1; echo $?)

if [ ! "$SSH_AUTH_SOCK" ] || [ $agent_run_state = 2 ]; then
    agent_start
    ssh-add
elif [ "$SSH_AUTH_SOCK" ] && [ $agent_run_state = 1 ]; then
    ssh-add
fi

unset env

If the file doesn't exist, you'll have to create it, which is difficult in Windows as it doesn't like having files with an extension but no name. To create it run:

    echo "" > .bashrc

Once you save that and exit, you'll be prompted once for your passphrase whenever you start a GitBash session, and then no more for the duration of that session.

VS Code

"Brilliant!" I hear you say! Not quite perfect yet though. Try doing a push or pull via VS Code now and you'll be greeted with an error message:

Example errors from two popular repos. Permission denied (publickey) for BitBucket and Host key verification failed for GitHub. Bummer.

Remember SSH-Agent runs under the agent that started it. VS Code isn't running one. The simplest way right now to get around this is to simply start VS Code from a GitBash session that is running the agent. Simply execute "code" in GitBash to start VS Code and try a push or pull again. Bingo.

So great. That should set you up nicely, though I'm not entirely happy with the workaround for VS Code. But maybe I'll address that in part 3. :)

Part 1: HTTPS
Part 3: SSH Config with multiple GIT accounts

Accessing GIT (Part 1) - HTTPS

So you've decided to help out on an open source project and clone a repository, or maybe start your own GIT repository somewhere like GitHub or BitBucket. Whatever the reason you'll need to decide on how to interact with your remote repository. HTTPS or SSH.

Let's play with both options. I'm assuming you have an account with your provider of choice. We'll use GitHub as an example but it won't matter if you have one of the alternatives. Just replace any mentions of GitHub with the name of your provider. I will also assume you have an existing repo as well. Gitbash will be our tool of choice.

HTTPS:

• No setup required and no special ports required. 
• Authentication is done via a username and password. 
• You can also add 2 factor (2 step) authentication for extra security. 
• Your login is the same as the one for logging into your account. So you use the same credentials for pushing, pulling, etc as well as logging into your account and changing account settings. Beware if your login is compromised.

I would suggest using 2-factor authentication and enforcing it at the account level so that all users have to use it. There will just be an additional authentication step, such as using an App on your phone to generate a code.

Get the clone URL for your repo from GitHub. Make sure you choose HTTPS. It should look something like this:

https://github.com/Username/Myrepo.git

Now clone it.

git clone https://github.com/Username/SandBox.git

If it's a public repo it'll clone. If not you should be prompted for a login. It's probably a public one, so let's do a push. Just add a file, commit it and do a git push. You should get a prompt like this:

2FA:

Of course, if someone were to learn your password the could wreak havoc in your code. Hopefully, you've configured 2 Factor (or 2 step) authentication. There is more than one way to do this so best refer to your provider of choice. Such as an app called Authy which generates access codes. If you have this set up there will be an additional prompt after submitting your credentials.

Credential Manager:

For every action you take against your remote repo, you need to submit your credentials all over again, which is a real ball ache, especially when you have 2FA enabled. Or at least it would be if you didn't have the help of Credential Manager. Remember when you installed GIT you should have seen something like this:

By default, "Enable Git Credential Manager" should be ticked. If it's not enabled, you can enable this later with the following line in Git Bash:

    git config --system credential.helper manager

Note: you may need to run as Admin to change any system config.

You'll also notice a difference in your login experience with it enabled vs disabled. Especially if you have 2FA where you might get an error if credential.helper is disabled.

If you want to play around and see how it looks, you can disable the credential.helper by unsetting it as follows:

    git config --system --unset credential.helper manager

Of course, if you have this set at local or global level as well as system, you'll need to do the same there.

You can also check out the Windows Credential Manager while you do this. Just search for an open "Credential Manager"

You can delete stored credentials from there while you experiment if you like, to force a credential prompt.

Once you've set it up, you should be good to go with VS Code as well. The same settings will apply there. But there is another method to connect. We'll cover SSH in part 2.

Part 2: SSH
Part 3: SSH Config with multiple GIT accounts

Dealing with Nulls in Check Constraints

A lot has happened since my last post.  I've moved to a fixer upper in a new city, changed my job and we've had a baby girl.  So I guess I shouldn't beat myself up for not sticking to my one post every fortnight rule.  Now with three under 5 kids, I suspect I still won't be making it, but here's an attempt to start :)

I recently noticed something I thought unusual with a check constraint.  One of the conditions of interest was

• len(someCol) > 5

Here's a pseudo line of the full condition set:

• ((Flag='Y' AND (String1='str1' OR String1='str2') AND len(String2)>(5)) OR (Flag='N' AND String1 IS NULL AND String2 IS NULL))

So you can see that it either needs to meet condition 1 OR condition 2, condition 1 being the one with the length function.

There were two things that I had to get to grips with:

1) I do not think that means what you think it means...

Null Values aren't dealt with the same by check constraints as they would as they would be in conditions in your regular code.  Check constraints pass a check, not when a condition is true, but when it is not false.  The difference is subtle but remember that SQL operates of three-valued logic.  There is True, False and also Unknown (i.e. NULL).

So if something is NULL, it is also "NOT FALSE" and will pass the check.  Be wary!!!  Here is some script to illustrate this.  

set nocount on;  --Let's get rid of those pesky row counts

declare @Flag char(1) = 'Y', @String1 varchar(10) = 'str1', @String2 varchar(10) = null;  --NULL is what we're mostly interested in here.

--Let's check the consition is some "regular" script.  Does it fail or pass?

if ((@Flag='Y' AND (@String1='str1' OR @String1='str2') AND len(@String2)>(5)) OR (@Flag='N' AND @String1 IS NULL AND @String2 IS NULL))print 'Check test condition passed.  String2 = ' + isnull(@String2,'NULL');else print 'Check test condition failed.  String2 = ' + isnull(@String2,'NULL');

--We'll need a table with a check constraint to test an actual check constraint

if OBJECT_ID('WhatTheHell','U') is not null drop table WhatTheHell;create table WhatTheHell( Flag char(1), String1 varchar(10), String2 varchar(10));alter table WhatTheHelladd constraint QuePasa check ((Flag='Y' AND (String1='str1' OR String1='str2') AND len(String2)>(5)) OR (Flag='N' AND String1 IS NULL AND String2 IS NULL));

--Can we pass the check and insert into the table with the same values?

insert into WhatTheHell (Flag,String1,String2) values (@Flag,@String1,@String2);

if @@ROWCOUNT = 1 --i.e. If the insert worked

print 'Check actual condition passed.  String2 = ' + isnull(@String2,'NULL');else print 'Check actual condition failed.  String2 = ' + isnull(@String2,'NULL');

Feel free to play around with the values, but running this as it is you'll see the following results from your testing:

Check test condition failed.  String2 = NULL
Check actual condition passed.  String2 = NULL

As you can see the results actually PASS the check constraint.  So be careful and always consider NULLs if your columns allow it.

2) The refactor factor

Have a look at definition of the check constraint created above using the following:

select definition from sys.check_constraints where name = 'QuePasa';

 Can you spot the difference?  It now looks like this:

([Flag]='Y' AND ([String1]='str1' OR [String1]='str2') AND len([String2])>(5) OR [Flag]='N' AND [String1] IS NULL AND [String2] IS NULL)

And before it essentially looked like this:

((Flag='Y' AND (String1='str1' OR String1='str2') AND len(String2)>(5)) OR (Flag='N' AND String1 IS NULL AND String2 IS NULL))

It removed some parentheses.  This is because SQL Server deemed these parentheses to be redundant, and they are according to the operator precedence.  AND takes precedence over OR.

Personally, I'll still use the "redundant" parentheses in my code as I find it easier to read and you don't always have to remember the precedence.

No longer mystified :)

Sources:

On check constraints: https://technet.microsoft.com/en-us/library/ms188258(v=sql.105).aspx
On operator precedence: https://msdn.microsoft.com/en-us/library/ms190276.aspx

IN / NOT IN vs EXISTS / NOT EXISTS vs INTERSECT / EXCEPT vs JOIN (Part 2)

EXISTS and NOT EXISTS:

We've already discussed IN and NOT IN in part 1.  We'll use the same examples tables from there, so please run those statement to create the temporary tables that we'll be using.  Everything below was done in SQL Server 2012.

EXISTS:

Exists is slightly different to IN.  It returns a true predicate as long as one row is returned from within the subquery, whereas an IN statement will match all values from the outer query to the values from the inner subquery.  So you should be able to see right away the benefit of using EXISTS over an IN statement.  All that SQL Server needs to do is find one row, then it can stop searching for further matches within the inner subquery.  This is commonly referred to as short circuiting and can make for a much faster query.

EXISTS also allows you to match against more than one column, whereas you would have to use multiple IN statement to achieve the same thing with IN, which would be very costly.  Have a look at the below example.  Try adding a second predicate in the subquery.

select *
from #products
where exists
(
select *
from #orderItems
where productName = #products.productName
);

Quite often I have seen developers avoid using a * in the inner subquery thinking this will produce less overhead.  For example putting in a "SELECT 1" instead.  There is no need to do this.  Remember the EXISTS is simply checking to see if a row from the subquery is returned.  It doesn't care about the columns, so doesn't include them anyway.  Try running one against the other and checking out the execution plans.  You'll see they will be exactly the same.

Of interest: You'll also notice in the execution plan, that it's done a TOP (1) on the subquery.

NOT EXISTS:

NOT EXISTS is similar to EXISTS, of course, but will only evaluate as true if NO results are returned by the subquery.  Try the following:

select *
from #products
where not exists
(
select *
from #orderItems
where productName = #products.productName
);

Now we don't have the same problem that we had with NOT IN when it returned nulls.  But what if we wanted to compare two similar tables to see if any rows differ.  For example, you are bringing in data from one system to another, but don't want to insert or update any rows if an exact matching row already exists in the destination table.

Let's make another product table and alter some of the data:

insert into #products (productName,price) values (null,0);
select * into #products2 from #products;
update #products2 set productName = null where productID = 6;

Now lets try and highlight any rows that differ between the two product tables when comparing against ProductID and ProductName:

select productID,productName from #products p1
where not exists
(
select *
from #products2
where productID = p1.productID
and productName = p1.productName
);

This returns the following:

Row 1:  Great.  It recognised that product 6 was different.  #Product2 had the product name for this item as null.  #Product did not.

Row 2:  Not what we wanted.  Product 7 is exactly the same in both tables, so why has it shown up here.  Remember what we discussed in the previous post about nulls and that they essentially mean "unknown."  When comparing two unknowns, the end result will also be unknown.  So it cannot match the two.

You could of course use the ISNULL function for both ProductName columns, but this adds overhead, especially if you have many columns to compare.

This leads us on nicely to the next chapter where I will go over EXCEPT and INTERSECT.

Till then, MERRY CHRISTMAS!

IN / NOT IN vs EXISTS / NOT EXISTS vs INTERSECT / EXCEPT vs JOIN (Part 1)

There are a number of ways of filtering your data according what exists or doesn't exist in another table or result set.  These are:

1. IN and NOT IN
2. EXISTS and NOT EXISTS
3. INTERSECT and EXCEPT
4. LEFT JOIN

In this series I will address the pros and cons of each, and how they handle nulls. I won't be discussing how you use them, but rather what they do and when to use them.

Let's start by prepping the tables that we'll use in the examples:

create table #orderItems ( orderItemID integer identity (1,1), productID integer, productName varchar(100), itemTotal decimal(6,2));
create table #products ( productID integer identity (1,1), productName varchar(100), price decimal(6,2));
insert into #products (productName,price) values ('Big Widget',39.99), ('Small Widget',12.95), ('Widget Multipack',72.95), ('Black Widget',14.99), ('Big Widget',39.99),  --Duplicate ('Super Widget',99.99);
insert into #orderItems (productID,productName,itemTotal) values (1,'Big Widget',35), (2,'Small Widget',12.95), (1,'Big Widget',39.99), (2,'Small Widget',12.95), (1,null,35), (5,'Super Widget',99.99);

The examples below will all be attempting to find the products that have had an order, or products that have not.  Note we have one duplicate product and we have one orderItem that has a null productName.  It's not very normalised, but for the purpose of this exercise we'll be matching on productName.

IN and NOT IN

These essentially just create a long list of AND or OR clauses and will of course return all rows, regardless of duplicates, from the containing query that match (or do not match) the resulting values from the subquery.  Lets look at IN first:

The following two queries are synonymous.  The second query is just to illustrate the logic of how the IN operator works.

select * 
from #products
where productName in (select productName from #orderItems);

select * 
from #products
where productName = 'Big Widget'
or productName = 'Small Widget'
or productName = 'Big Widget'
or productName = 'Small Widget'
or productName = null
or productName = 'Super Widget';  

The IN operator essentially creates a bunch of OR EQUAL TO conditions and a result is returned.  Now let's try NOT IN.

Again, the following two queries are synonymous:

select * 
from #products
where productName not in (select productName from #orderItems)

select * 
from #products
where productName <> 'Big Widget'
and productName <> 'Small Widget'
and productName <> 'Big Widget'
and productName <> 'Small Widget'
and productName <> null
and productName <> 'Super Widget';

Now for a NOT IN, it creates AND NOT EQUAL TO conditions.  Because these are now AND conditions, all of the results have to equate to true.  What happens when you run either of these queries?  What did you expect? Instead of seeing the two products that have no orders as you might expect, there are no results at all.  Can you see why? Remember that a null is an unknown value.  Nothing can equal, or not equal, an unknown.  So "productName <> null" does not return as true and because all results in the subquery set must be true, no result is returned.

Summary:

• Be wary of nulls when doing a NOT IN.  Know your data if you plan on using NOT IN.
• All rows will be returned. If you wanted distinct product names you would would have to use something like a distinct statement.
• You can only match on one column.

Next:  EXISTS and NOT EXISTS