What is not null?

From my understanding, when you write not null in the sql query, does it mean that the user must fill out that table as it is not empty? Is this the same as unchecking the null option?

Short answer: NOT NULL means that the returned values have a value in them.

Long answer: There are two basic scenarios where NULLS come into play (I’m sure there are more than two, but these are the two that are run into probably 90% of the time)

SCENARIO 1: A table allows NULLS into a field.

Depending on how your table is set up in the the database, a field may be set up to allow for NULL values. These fields are typically character based fields (CHAR/VARCHAR, TEXT, NTEXT). You can have them on numeric fields as well, but that’s not (at least in my experience) typically done because then it has to be accounted for in whatever coding language is being used.

NULLS are typically allowed whenever you don’t care if there is a value or not - optional fields for lack of a better term. So if you see a query like this:

SELECT field1
     , field2
  FROM tableName
 WHERE field1 IS NOT NULL

All that query is asking for is to return all rows that have ANY value in field1. It could be “1”, “olly olly oxen free”, “great googly moogly”. Whatever - it’s just looking for a value.

A little note. If a person puts a space in the field, the record won’t be returned here. So even from a humanistic approach, the field may be empty if looking at the database visually, from a database perspective it’s not null because there is a value in the field.

SCENARIO 2: JOINING tables together in a query

The other scenario where NULLS are typically encountered is when you join tables together, but it only comes into play in one type of join - the OUTER JOIN.

The difference between an INNER JOIN (short hand as JOIN) and an OUTER JOIN is that an inner join will return only the rows that match between the two joined tables. So if we have something like this

Table 1                               Table 2
field1 | field3                     field2 | field3
-----------------                   ---------------
   1         3                         11      3
   2         3                         12      3
   3         5                         13      7
   4         6                         14      8

An inner join would only return this:

field1 | field2
---------------
   1       11
   2       12

But what if you wanted to see ALL the rows in table1, regardless of there is a match in table2? That’s where an OUTER JOIN comes in

SELECT t1.field1
     , t2.field2
  FROM table1 t1
  OUTER JOIN table2 t2 ON t1.field3 = t2.field3

That would return

field1 | field2
---------------
   1       11
   2       12
   3      NULL
   4      NULL

But what if you wanted to see only those rows on table1 which don’t have matches?

SELECT t1.field1
     , t2.field2
  FROM table1 t1
 OUTER JOIN table2 t2 ON t1.field3 = t2.field3
 WHERE t2.field2 IS NULL

which would return

field1 | field2
---------------
   3      NULL
   4      NULL

Or what if you wanted to those rows on table2 which don’t have matches? That’s where a RIGHT OUTER JOIN comes in

SELECT t1.field1
     , t2.field2
  FROM table1 t1
 RIGHT OUTER JOIN table2 t2 ON t1.field3 = t2.field3
 WHERE t1.field1 IS NULL

which would return

field1 | field2
---------------
  NULL     13
  NULL     14

Notice that the where query says IS NULL That’s because if you were looking for NOT NULL, you’d want to use the INNER JOIN because if the second field has a value, then the match between the two tables has been made.

A little note #2. You’ll notice that one query uses OUTER JOIN and the other uses RIGHT OUTER JOIN. Technically the OUTER JOIN is a LEFT OUTER JOIN, but the DBMS by default uses OUTER JOIN as short hand for LEFT OUTER JOIN. So

SELECT t1.field1
     , t2.field2
  FROM table1 t1
 OUTER JOIN table2 t2 ON t1.field3 = t2.field3
 WHERE t2.field2 IS NULL

is the same as

SELECT t1.field1
     , t2.field2
  FROM table1 t1
  LEFT OUTER JOIN table2 t2 ON t1.field3 = t2.field3
 WHERE t2.field2 IS NULL

inner joins are the same way. All standard DBMS has short hand for inner joins, so JOIN = INNER JOIN = LEFT INNER JOIN. There is ALSO a RIGHT INNER JOIN, but in my 20+ years of database work, I’ve never encountered a situation where it was needed. In fact, the one time I saw it used, it was being used incorrectly, and the data being returned was wrong. :shifty:

So, I know this answer was a lot longer than you needed, but hopefully you’ll get what you needed out of it.

3 Likes

Thanks for the detailed explanation… i posted another question with regard to my piano website and i posted my tables there… i will post it here again but i have 2 tables, one called users and the other user_access, which has id and access_type (primer level, level 1, level 2 and level 3). All users can access primer levels but must pay to use level 1, 2 and 3… should i also have a namd or uid fir user_access? In order to use inner join, must there be a common column?

Yes.

Depends on how you want the security to work. If the levels are finite (i.e. level 2 > level 1 > primert) , then you can add a user_access_id to the user table. However, if level 1 has access to one set of content, level 2 has access to a completely different set and the user can purchase multiple sets, then you’re actually going to want to have what’s called a lookup table which bridges the two. The lookup table would have two columns, one which holds the primary key of the user table, and one which holds the primary key of the user_access table.

Thanks for the information…My business is more related to your 2nd point where users can subscribe for multiple purposes… would i use an inner join or left join for this? I have used a left join and seems ok…

A left join IS an inner join by default. Left join simply determines which table is the “starting” table that is joining from. The left table is the one that is on the FROM portion, the right is on the join statement.

An inner join only returns the records that meet the criteria.
An outer join will return all of the tables from the starting table (typically the left) and any matches from the right.

I would also like to upload some videos based on the following categories: primer level, level 1, level2 and level 3… I guess that I can also create 4 more tables and calling them primer level contents etc. For each table, i could name each video and have description but is there a way for me to keep track of which videos they have watched?

unless one already knows exactly how they differ, this is dangerously misleading

actually all tables are in the FROM clause

the left table is the one that comes before LEFT OUTER JOIN, and the right table is the one that comes after LEFT OUTER JOIN

(nitpicking) return all of the rows

the “starting” table (the one that is mentioned first in the FROM clause) is not necessarily the one that the optimizer chooses to access first

although it’s still a great idea to put the table which has the most restrictive WHERE conditions as the starting table

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.