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