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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can remove the nulls if you want to. it just returned 2 columns with null as the values
1
Julian HansenCommented:
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
Olaf DoschkeSoftware 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BRMarketingAuthor Commented:
Thank you All.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.