![](https://steemitimages.com/DQmTBV3SSA4aSPPieGL3GxrEwH5JSvpuXfTgsWiLyrQeiB1/image.png)
[Source](https://imgflip.com/i/222wd0)

Good Morning, Afternoon, Evening, Happy Holidays, Good Vacation, etc.

So here we are again, talking again about SQL Queries and Logic. It was brought to my attention by @bronevik (thank you for pointing that out to me, by the way) that my previous lesson may have jumped-the-gun as there are a couple of other SQL statements that should have been explained before it. I always like to see where I can do better with my teaching... heck, I'm only Human and sometimes I assume understanding where I should not.

So it is with pride that I make myself better and backtrack to teach the concepts and logic behind the 2 statements that @bronevik has pointed out to me.

___

![](https://steemitimages.com/DQmVu4cdtaqLpNkFptrPB31AhEBnkoiVDAVscjA9NLHrP1C/image.png)
[A Null Pointer... Programmer Humor that you might understand after this section](https://www.google.com/search?q=null&source=lnms&tbm=isch&sa=X&ved=0ahUKEwiiirTx1LzYAhXp6YMKHZPnDVwQ_AUIDSgE&biw=1280&bih=633#imgrc=WkfauAbiiK8PsM:)

_**The Basic Concept of NULL**_

It occurs to me that I probably have to step back first to explain to some what the term _NULL_ actually means. The _NULL_ keyword is, essentially, a way to say "There is NOTHING here." What this means is that there is absolutely no value. I know, this sounds like a pretty basic concept, but it goes deeper than what you might consider as nothing... it is not 0, it is not an empty text field, it is not an empty container. Basically there _is_ no container, it takes away even the concept of 0 or the empty text field. It's like a Time Traveler went into the past _after_ the Query ran and removed the field completely. There's no value, no whole, no negative space, no _anything_.

As is defined well in the Wikipedia post [Here](https://en.wikipedia.org/wiki/Null_(SQL)):

_"Null (or NULL) is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfil the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. In SQL, NULL is a reserved word used to identify this marker."_

The problem with _NULL_ values lies in the fact that most early programming languages, spreadsheets, or data manipulation tools cannot truly understand the _non-value_ that _NULL_ represents. Therefore, programmers and data analysts usually (it doesn't always happen, but it's a good "best practice" to consider) check against data types being _NULL_. Newer Programming Languages have been set up with a way to handle _NULL_ "values" in queries and data returns.

___

https://youtu.be/HkZeUa53Jyg
[Source](https://www.youtube.com/watch?v=gn6BDeKn-qc)

_**"You have Sunk my Battleship... IS NULL?**_

Even DEATH may be confused as to this one. If Bill & Ted were playing a game and had truly sunk DEATH's Battleship, would it be NULL? This is going to be funky (made up table names and fields):

_SELECT status FROM BogusGames WHERE player = 'DEATH' AND game = 'BattleShip' AND token = 'BattleShip' AND hit_count = '4' AND wins IS NOT NULL_

The above query would never return a row with the _wins_ field holding a _NULL_ value. This means that the field will always have some sort of value. _IS NOT NULL_ filters the query by telling the _DBMS (Database Management System)_ not to return any rows if the _wins_ field has not been set at all (and this does not mean the field is blank... it is not even blank).

Additionally, you could query with _IS NULL_ as the filter if you wanted to find out how many rows have the field actually set to _NULL_ specifically. This can be done to verify data and _UPDATE_ rows when the field value(s) contain(s) _NULL_ values.

_SELECT status FROM BogusGames WHERE player = 'DEATH' AND game = 'BattleShip' AND token = 'BattleShip' AND hit_count = '4'_

This query, on the other hand, could conceivably return a _NULL_ value if the _wins_ field has not been set at all for the returned results. Mind also that if the field is set to _allow NULLs_ then the person setting the data could intentionally leave it as a _NULL_ value.

https://youtu.be/Z3sLhnDJJn0
[Source](https://www.youtube.com/watch?v=3EkBuKQEkio)

___

_**The IN Crowd**_

This alternate method to using any _JOIN_ statements can be used to filter data based on a _subquery_ that defines a separate list of results. A _subquery_ is literally a subordinate query that is run first in order to find a set of values to filter based on. It's a great way to get into filtering queries with other database tables without having to _JOIN_ the Dark Side. An example is below:

![](https://steemitimages.com/DQmRHSuyoxxBfS2QGRFVjs9yZCQs6rr7kzxA2caJkp7j57T/image.png)

As you can see above, I ran 4 different queries to show data. I am using the SchoolChildren and BackPack tables to show how to get data from one table using a _sub-query_ from another table along with the _IN_ SQL statement. This will help you understand how this is used to limit the initial data.

You can see the initial 2 queries (_select * from SchoolChildren_ and _select * from BackPack_) at the top and the corresponding results are the 2 upper sets of results. Basically it says "return everything" from each of those queries. This is like my scientific Baselines... it returns everything so you can check the results of the other queries to verify the data if you feel the need.

The 3rd query is the bulk query utilizing the _IN_ statement to add a _sub-query_ to the mix in order to filter the base query by the results of the _sub-query_. The 4th query is just added to isolate the _sub-query_ so that you can see the results.

So... what happens when the larger query with the _sub-query_ is executed is that it, first, queries the _sub-query_ to find the filtered range of data. You can see the results of that query as the lowest set of results. Now it puts these result items in for the _WHERE_ clause, so it effectively reads like this afterwards:

_SELECT * FROM BackPack 
WHERE id IN ('1', '4', '5')_

*Note: this is a viable option as well, if you know the values to query against.

In essence, it says "find all the results that have an _id_ value of 1, 4, or 5 and show all corresponding fields from the BackPack table". It is a perfectly acceptable way to handle data and a great way to filter out unwanted material _before_ the larger mass of data is returned. Doing this will make the queries run faster and is one step to optimize queries for speed and efficiency.

![](https://steemitimages.com/DQmUq6iTAUjUQfTTBXJik4pZbpVd5MwJqe2RJDbNgwz9xMD/image.png)
[Dora's Backpack... could be what you're trying to find?](https://www.google.com/search?biw=1280&bih=633&tbm=isch&sa=1&ei=xUFNWoznKZ22jwSO6q7QBQ&q=backpack+dora&oq=backpack+dora&gs_l=psy-ab.3..0l8j0i5i30k1l2.10181.13326.0.13581.7.6.1.0.0.0.101.493.5j1.6.0....0...1c.1.64.psy-ab..0.7.502....0.JBd-4EcpHFs#imgrc=zJCJodOYQOPXSM:)

One note about using _sub-queries_ is that you are only allowed to return a single field's values to compare to the field just before the _IN_ statement (in this example, it compares the _id_ results from the SchoolChildren table with the _backpack_id_ field in the BackPack table).

Before getting overly complicated and compiling difficulty, I just want to take a step back and look at the big picture.

![](https://steemitimages.com/DQmcud7AGWTzzkr6V7F5iiWLPjrPbWwbwCBWCXDeq5EtSjK/image.png)
[I think I have an idea!](https://www.google.com/search?biw=1280&bih=633&tbm=isch&sa=1&ei=1EFNWuTyDqHLjwSu9bzoAQ&q=big+picture&oq=big+picture&gs_l=psy-ab.3..0l10.71724.72905.0.73160.11.8.0.3.3.0.97.602.8.8.0....0...1c.1.64.psy-ab..0.11.612...0i67k1.0.aoAf5A-boTw#imgdii=LpcqI1CmhbXQpM:&imgrc=Txh7Cjnjll-arM:)

Yep, that was a rather large picture. I liked it.

___

Thanks to everyone for making my Tutorials at least a minor success. I appreciate all the views and votes of confidence. 

If you want me to focus on anything in particular or go back to anything, just leave a comment and inquire into it. We're still just scratching the surface.

![](https://steemitimages.com/DQmZx1n2vmjcC9irVnfzSLJCRRQhvK8QXv4otf3uebKCZoJ/image.png)
[We'll look deeper](https://www.google.com/search?biw=1280&bih=633&tbm=isch&sa=1&ei=skNNWrCcJpfijwTKsI7gAQ&q=spy+with+magnifying+glass&oq=spy+with+magnifying+glass&gs_l=psy-ab.3..0j0i5i30k1.5843.7274.0.7768.3.3.0.0.0.0.125.267.2j1.3.0....0...1c.1.64.psy-ab..0.3.266...0i7i30k1j0i7i5i30k1.0.0gyFCpdBUZ8#imgrc=3r9wEroworK3LM:)

___

If you need refreshers or need to go back to read some of my earlier posts in this Series before continuing onward, please feel free to do so now. Here is a list of links to those Posts:

[SQL Beginner's Tutorial: Relational Databases & SQL](https://steemit.com/steemiteduction/@dbzfan4awhile/sql-beginner-s-tutorial-relational-databases-and-sql)
[SQL Beginner's Tutorial: Writing your First Queries](https://steemit.com/sql-forbeginners/@dbzfan4awhile/sql-beginner-s-tutorial-writing-your-first-queries)
[SQL Beginner's Tutorial: SELECT Data to Use in the Game](https://steemit.com/utopian-io/@dbzfan4awhile/sql-beginner-s-tutorial-select-data-to-use-in-the-game)
[SQL Beginner's Tutorial: Manipulate Data for Use in the Game](https://steemit.com/utopian-io/@dbzfan4awhile/sql-beginner-s-tutorial-manipulate-data-for-use-in-the-game)
[SQL Beginner's Tutorial: JOIN Me and Together We Will Rule the Universe](https://steemit.com/sql-forbeginners/@dbzfan4awhile/sql-beginner-s-tutorial-join-me-and-together-we-will-rule-the-universe)