SQL Beginner's Tutorial: What if the World IS NOT NULL? and IN the Galaxy?

Source

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.

---

A Null Pointer... Programmer Humor that you might understand after this section

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:

"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.

---

Source

"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.

Source

---

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:

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.

Dora's Backpack... could be what you're trying to find?

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.

I think I have an idea!

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.

We'll look deeper

---

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
SQL Beginner's Tutorial: Writing your First Queries
SQL Beginner's Tutorial: SELECT Data to Use in the Game
SQL Beginner's Tutorial: Manipulate Data for Use in the Game
SQL Beginner's Tutorial: JOIN Me and Together We Will Rule the Universe