mysql select nested statement

Dear Experts,
I want to learn why I need to use NULL twice for this nested statement? what is NULL for in this statement?
SELECT table1.uid, 
       table1.isim, 
       NULL, 
       NULL 
FROM   table1 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   table2 
                   WHERE  bayikodu = table1.uid); 

Open in new window

LVL 1
BRMarketingAsked:
Who is Participating?
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
Julian has described the result of this as 2 extra columns well.

The meaning of values instead of field names in the field list part of a SELECT SQL query is to generate a field on the fly without getting it from database tables. A constant value is put into every result row as an extra column, Instead of a constant as NULL is, functions could be used to generate values depending on other columns, so in general, these are like computed fields. Albeit not stored in the database (that's a concept in other databases, i.e. MSSQL).

In the normal usage of such expressions, these extra columns are also named via AS name clauses, because whatever code uses the query result will access columns by names and not specifying them the query means the DB SQL engine will automatically name them. Whatever code now makes use of these extra columns now relies on how these columns are named automatically.

What's also bad about this specific usage of such computed result fields is the missing specification of the columns data type. NULL, as you likely know, is the value indicating no value, missing data, it's usable as special extra value for all data types, as long as columns are designed nullable. So it would be good practice to cast this NULL as a certain data type, which means the result has a nullable column of that type and the null could be replaced by values of that type. This way I'm not even knowing what type MySQL generates, there is no NULL only field type, so I guess it'll use something typical, eg char field, but what size?

So finally the way this query is written is not recommendable. If this works out okay, then this is based on several default behaviors, which could change with MySQL version or even just driver changes. But yes, whether or not you may change or remove these NULLs strongly depends on whatever is using the query result needs. If you have hands on that side of the code and see it refers to these fields with certain names, it would be a good move to not rely on automatic naming but add these names within the query itself, when you see what that code does with these two extra columns. It's likely these columns are initialized NULL to be set to concrete values afterward, so knowing which data type is stored in them and then changing the query to prepare that data type explicitly would also raise the quality and stability of this to be stable against behavior changes of versions or drivers.

Bye, Olaf.
1
 
Ryan ChongConnect With a Mentor Commented:
you can remove the nulls if you want to. it just returned 2 columns with null as the values
1
 
Julian HansenConnect With a Mentor Commented:
I am guessing it is being used to pad the results - to make sure the query returns 4 columns even though there are only 2 result columns.

Could be that whatever is using the query requires a result with 4 columns.

We would have to see where it is being used to be sure.
1
 
BRMarketingAuthor Commented:
Thank you All.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.