Lets say i have a Excel sheet , col : name and ID . some rows cells in "name" are empty in datasheet.

now sql pulls this data, will the empty cells be filled with a null value by default?

or is null a unique type of character that is only used to overwrite existing values , so when system reads it it thinks its empty?

I have a table which contains some values which are not null. I thought it might be an empty sting but it was not an empty string either.

I copied that particular field from the table and tried an update statement to convert it into null but it didn't worked either . Kindly suggest something

• Where and why exactly a null is used?

• What is exactly null and not null? To my understanding Not null we use when its mandatory to insert some value in that field, also when we give check constraint so by default the column will be not null right?

• By adding new column through alter method default values are null, so how would I be able to insert values in it and is it right to give not null constraint to that new column while adding through alter method, basically when null and when not null to be used?...

god this is so confusing please help me, ik im asking alot but im really confused

The way SQL handles null strings is just lousy for typical CRUD use (business & administration). Roughly 99.9% of the time what's really needed is for null strings to be processed & treated just like a zero-length string ("blank" for short). Having a function or expression to detect whether it's "null" or not would serve fine when explicit null detection is needed. (Other than maybe JOIN-related expressions, I'd almost never need it.)

For example, under most SQL dialects, if you concatenate strings A, B, and C; and say B is null, the ENTIRE expression is null regardless of what A and C are. I've never ever needed this behavior. And the 1 out of gazillion times you do, the null-checking function/expression can ask. Thus, queries end up cluttered with COALESCE, NVL, etc. de-null-er functions. Repetition of code or code patterns is usually a sign your language or API's are a poor fit for the need.

Further, when you see something like this:

   CREATE TABLE Persons (
     ID int NOT NULL,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255) NOT NULL,
     Age int);

The vast majority of the time you really want is "not blank", or more specifically not to have just "white space". There is a wonderful C# function called "IsNullOrWhiteSpace()" that fits the kind of constraint that's really desired. I wish the standard would be modified to have a "NOT EMPTY" constraint:

   -- What we need & want vast majority of the time
   CREATE TABLE Persons (
     ID int NOT NULL,
     LastName varchar(255) NOT EMPTY, /* Can't be null or just white spaces */
     FirstName varchar(255) NOT EMPTY,
     Age int);

Maybe there's a better word choice than "Empty", but this a starting point for pondering and discussion.

Justifications for the status quo are usually academic gobbledygook. To be frank, people in school too long often lose sight of the practical world. I'll lose reddit score for claiming that, but keep seeing it, and not just with nulls. I'm paid to make my boss happy, not professors.

[Edited.]

Hi! Me and my sibling-in-law are just beggining to learn SQL and are about to get in a boot camp that gives you an introductory "exam". We failed it the first time, but weren't told why. This Exam willl change, so we're not looking to have our homework done so to say, we just want to understand what we did wrong in the first try.

And after watching a lot of videos and trying different solutions, we're a bit confused about this schema:

What we can't get a grasp on is what's the use of NOT NULL here? Like, how should we add that to our querys?

We're also a bit lost when it comes to item 10, how should we use "join" here?

Thank you in advance, we're doing our best!

I'll translate all the questions so that there's some context:

The first point was:

"Write an SQL query to show all the products in the table "Productos" with a price higher to $50."
Our answer was:

Select * from productos where Price > 50

Second point was:
"Write an SQL query to obtain the total amount of orders (pedidos) made by an specific client according to his ID"
Our answer was:

Select cliente_ID, count(*) as Pedidos_count
from Pedidos
where cliente_ID= ‘NOT NULL’
group by cliente_ID

Third point was:
"Write an SQL query to update the price of a product on the table "Productos""
Our answer was:

Update productos set price = ‘Float’
where nombre = ‘Varchar’

Fourth point was:
"Write an SQL query to show the names of the products together with their corresponding categories."
Our answer was:

Select nombre_varchar, categoria_varchar from productos

Fifth point was:
"Write an SQL query to delete all the orders that have an amount lesser than 5."
Our answer was:

Delete from pedidos where quantity < 5

Sixth point was:
"Write an SQL query to calculate the total price of the orders made."
Our answer was:

Select SUM (total_precio) as "total_pedidos_precio"
From Pedidos

Seventh point was:
"Write an SQL query to show the names of the products in ascendant alphabetical order."
Our answer was:

select * from productos
Order by nombre asc

Eighth point was:
"Write an SQL query to show the orders made in a specific date." (fecha means date).
Our answer was:

select * from Pedidos where date (fecha_pedido) = NOT NULL

Ninth point was:
"Write an SQL query to obtain the average of the prices of all the products."
Our answer was:

Select AVG (precio) from Productos

Tenth point was:
"Write an SQL query to show the products together with the total amount of orders made for each one."
We weren't sure about this one, we think we have to use the join clause, but we couldn't agree on how to.

Eleventh point was:
"What's the correct syntax to insert a new record in the table "Usuarios" (Users)"
a) INSERT INTO Usuarios (Nombre, Apellido) VALUES ('John', 'Doe'); (Picked this one)
b) INSERT Usuarios (Nombre, Apellido) VALUES ('John', 'Doe');
c) INSERT VALUES ('John', 'Doe') INTO Usuarios;
d) INSERT INTO Usuarios VALUES ('John', 'Doe');

Twelfth point was:
"What's the function used to obtain the total amount of records in a table?"
a) COUNT() (Picked this one)
b) SUM()
c) AVG()
d) MAX()

Thirteenth point was:
"What's the clause used to filter results in a SELECT query?"
a) WHERE (Picked this one)
b) FROM
c) ORDER BY
d) GROUP BY

Fourteenth point was:
"What's the operator used to combine conditions in a WHERE clause?"
a) OR
b) AND (Picked this one)
c) NOT
d) XOR

Fifteenth point was:
"What's the SQL query to delete an existing table?"
a) DELETE TABLE name_table; (Picked this one)
b) DROP name_table;
c) REMOVE name_table;
d) ERASE name_table;

I'd rather not write a bunch of AND clauses, so is there a quick, efficient way to do this?

I'm importing some data with 10 fields into a SQL Server from a CSV file. Occasionally this file has null/empty values across all the cells/columns.

What I'd like to do is just write one relatively short sql statement to simply count (at first) all these rows. I'd rather do it without doing something like:

...and (column1 is null or column1 = '')
...and (column2 is null or column2 = '')

etc...

Is there a good way to do this, or am I stuck with the above?

I was attempting to write something along the lines of the below to delimit 2 columns if column1 wasn't null.
select iif(ifnull([column1]), '', [column1] + '|') + [column2]

and i settled on something like this in MS SQL Server
select isnull([column1] + '|', '') + [column2]

It seems to work for me as anything concatenated with a NULL is a NULL. I know it limits the output of the second parameter to only 2 character (due to the first parameter being 2 characters), but that's NBD because I'm only outputting an empty string anyway. Anything I'm missing or is this bad form? should I be using case or something else? Thank you

As far as I can tell, the easiest way to check a list of values for a NULL is to concatenate the strings, then check for a null.

SELECT  CASE WHEN 'a' + NULL + 'string' IS NULL THEN 0 ELSE 1 end

Results in 0

SELECT  CASE WHEN 'a' + string' IS NULL THEN 0 ELSE 1 end

Results in 1

If you’re trying to find nulls in a mixed bag (numbers and strings), you can add your numerics, then convert them to a string to concatenate with everything:

SELECT CASE WHEN 'a' +  'string' + CONVERT(varchar(20), 1 + null + 3) IS NULL THEN 0 ELSE 1 END

Results in 0

SELECT CASE WHEN 'a' +  'string' + CONVERT(varchar(20), 1 + 2 + 3) IS NULL THEN 0 ELSE 1 END

Results in 1

I’m sure you hate this though, but is there an easier way?

I'm looking for some best practices guides or arguments for/against different designs to solve this problem:

In short: if a string value is optional, make it required and use an empty string, or make it nullable and use null and don't allow empty strings? I assume #1 is the answer but I want to get a feel for what people think, or if there's something new I don't know about.

In full:

I have a server inventory database with some user configuration tables. One table controls expected hostnames across all environments. I have two tables: "HostnameFamily" and "Hostname".

HostnameFamily
- FamilyId [PKEY]
- FamilyName
- (Other Configuration Columns)

HostnameEnvironment
- FamilyId [PKEY]
- EnvironmentName [PKEY]
- Hostname

Through a SQL view this generates a list of all expected hostnames across all environments. Example names are: appserver-dev1, appserver-staging, appserver-production, webserver-dev1, webserver-staging, etc. To make configuration easier and since most follow patterns I allowed * to be set for EnvironmentName and "%env%" in the Hostname to automatically generate names for all environments that didn't have an explicit name, also handled through the view. Not all families have a * entry because some are one-offs for specific environments.

Here's where my question starts. I want to move the * environment pattern out of HostnameEnvironment because I'm expanding the environments this covers greatly and need a foreign key constraint on the EnvironmentName column.

My thought is to add a DefaultPattern column to HostnameFamily, but not all HostnameFamily records have the * pattern so I need to handle this somehow. I assume the preference is to make it required and use an empty string if a default isn't desired? Or is there another preferred way to toggle functionality?

Hello,

I've got a database of time stamped events from a youth soccer game.

So far, I have imported the CSV of data into MySQL Workbench.

Here are the datatypes affiliated with the columns:

Here is what my database looks like:

Data, unaltered:

The statement I’m trying to get to work is:

Select * From game_2_data where Player IS NOT NULL;

All that seems to get me is the original data (inclusive of rows that contain blanks in the Player column).

The following statement does work:

Select * From game_2_data where Player = “John”;

I assume I’m overlooking something simple.  Thanks for any pointers!     

Hi awesome community, As far as i have understood the Null Values in SQL is a field with no value. An unique constraint allows one null value. Unique constraint scans the whole column correct me if i am wrong. If we are entering the second null value in the column. How does the database engine compares the two values if it is null or not because Null Values in SQL is a field with no value. Thanks in advance.

I'm providing some basic database training to some colleagues who are new to the topic. They're having difficulty understanding the difference between NULL and an empty string and I'm not doing a very good job of explaining it! Does anybody know a good way to explain it to beginners, specifically in a database context?

Is there ever a time when you'd prefer empty values of a varchar to be an empty string instead of a NULL?

I was helping someone with an old php project and they decided to just convert all empty strings from the web form into NULL in the database and they modified all fields of all types on all tables to accept NULL.

I don't know enough about databases to say if this was a good or bad idea. What do you think?

I need a select statement that can search by a value even if that value is null but i dont know how to make it. Tjis is what im trying to do: Select * from People where name = 'nameValue' And nameValue could be 'steve' 'mark' or null. I need go be able to search even if the value is null. Can someone please help me?

I remember what I was taught in college, but that was years ago. The professor at the time said to avoid it at all costs. I'm curious what the current consensus is.

I am working on a bunch of orders and I have an order date and the date that the order was "purchased". (The day we processed the order). If we never processed the order because the customer never followed through, there is a blank spot left in the purchased date resulting in a null when I run my query. I attempted to use IS NOT NULL to filter it out in the where statement but nothing happened. Any thoughts?

WHERE
DATE(a.ORDER_DATE) BETWEEN DATE(:startDate) AND DATE(:endDate)
AND
a.PURCH_ORDER_DATE IS NOT NULL;

EDIT: For all of those who are wondering, I figured out how to sort out the null values.

SELECT DISTINCT
a.ORDER_NUMBER
,a.SALES_CATEGORY
,a.ORDER_DATE
,a.PURCH_ORDER_DATE
,CASE
    WHEN DATEDIFF(a.ORDER_DATE,a.PURCH_ORDER_DATE) >= 0 THEN DATEDIFF(a.ORDER_DATE,a.PURCH_ORDER_DATE)
    ELSE "null"
 END as Lag
FROM
SOBOOK a

WHERE
DATE(a.ORDER_DATE) BETWEEN DATE(:startDate) AND DATE(:endDate)
AND
ceil(DATEDIFF(a.ORDER_DATE,a.PURCH_ORDER_DATE)) = DATEDIFF(a.ORDER_DATE,a.PURCH_ORDER_DATE)

ORDER BY
 ORDER_NUMBER
,Lag

I am working on a query where I need to try and return the date column as Null even if it has a date in it.

The query needs to search 'status' for '5' then returns the column end date as null if it is 5 and leave it if it isn't 5.

I have a tried a few different ideas and haven't managed to get them to work wondering if anyone else might be aware on how to do this?