Menu
In fact, according to your query, the SELECT 0 only verifies the conditions in your WHERE clause. So if the condition is true, your sub query will return 0 . Then the NOT EXISTS will check if your sub-query is returning a value.. IN other word, it will DELETE if the conditions set in the subquery does NOT EXIST .
By using SELECT * , you can return unnecessary data that will just be ignored. But fetching that data is not free of cost. This results in some wasteful IO cycles on the DB end since you will be reading all of that data off the pages. Perhaps you could have read the data from index pages.
We can return 0 for NULL in MySQL with the help of IFNULL() method. The syntax of IFNULL() is as follows. IFNULL(YOUREXPRESSION,0);
When selecting data from a table, there might be some NULL values that you don't want to show, or you want to replace it with 0 for the aggregate functions. Then you can use COALESCE to replace the NULL with 0. For example, we have the table salaries with 5 columns: emp_no , from_date , to_date , salary , bonus .
Lets say I have a SQLite database that contains a table:
sqlite> create table person (id integer, firstname varchar, lastname varchar); Now I want to get every entry which is in the table.
sqlite> select t0.id, t0.firstname, t0.lastname from person t0; This works fine and this it what I would use. However I have worked with a framework from Apple (Core Data) that generates SQL. This framework generates a slightly different SQL query:
sqlite> select 0, t0.id, t0.firstname, t0.lastname from person t0; Every SQL query generated by this framework begins with "select 0,". Why is that?
I tried to use the explain command to see whats going on but this was inconclusive - at least to me.
sqlite> explain select t0.id, t0.firstname, t0.lastname from person t0; addr opcode p1 p2 p3 p4 p5 comment ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 0 Trace 0 0 0 00 NULL 1 Goto 0 11 0 00 NULL 2 OpenRead 0 2 0 3 00 NULL 3 Rewind 0 9 0 00 NULL 4 Column 0 0 1 00 NULL 5 Column 0 1 2 00 NULL 6 Column 0 2 3 00 NULL 7 ResultRow 1 3 0 00 NULL 8 Next 0 4 0 01 NULL 9 Close 0 0 0 00 NULL 10 Halt 0 0 0 00 NULL 11 Transactio 0 0 0 00 NULL 12 VerifyCook 0 1 0 00 NULL 13 TableLock 0 2 0 person 00 NULL 14 Goto 0 2 0 00 NULL And the table for the second query looks like this:
sqlite> explain select 0, t0.id, t0.firstname, t0.lastname from person t0; addr opcode p1 p2 p3 p4 p5 comment ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 0 Trace 0 0 0 00 NULL 1 Goto 0 12 0 00 NULL 2 OpenRead 0 2 0 3 00 NULL 3 Rewind 0 10 0 00 NULL 4 Integer 0 1 0 00 NULL 5 Column 0 0 2 00 NULL 6 Column 0 1 3 00 NULL 7 Column 0 2 4 00 NULL 8 ResultRow 1 4 0 00 NULL 9 Next 0 4 0 01 NULL 10 Close 0 0 0 00 NULL 11 Halt 0 0 0 00 NULL 12 Transactio 0 0 0 00 NULL 13 VerifyCook 0 1 0 00 NULL 14 TableLock 0 2 0 person 00 NULL 15 Goto 0 2 0 00 NULL If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With