Quote:
Originally Posted by Banana
Apparently, Wordpress is powered by MySQL, but as I posted above, SELECT 1 construction doesn't work; it will return all rows, displaying same value '1' for each row. I wonder if this could actually be a stored procedure or something like that?
[...]
EDIT: I think I'm just slow. It looks like SELECT 1 is supposed to replace SELECT * to provide better performance
|
As chucker said, that's basically the idea.
This is why it's useful in situations like the exists + subselect because you don't need to keep the values in the subselect; you only want to know that they exist.
This idea is also useful for inserts + selects. Say you're working at a car dealership and you have a table "car_type" that contains columns make, model, and year and you have to manually update the database every year with the new models. Let's assume that you have the following data:
Code:
make | model | year
-------+--------------------+------
Honda | Civic Sedan | 2008
Honda | Civic Coupe | 2008
Honda | Civic Hybrid Sedan | 2008
Honda | Accord Sedan | 2008
Honda | Accord Coupe | 2008
Honda | Fit | 2008
Honda | Fit Sport | 2008
You get news that Honda's 2009 lineup is exactly the same as the 2008 lineup and you need to update the database accordingly. What do you do?
You can either do a whole slew of inserts...
Code:
INSERT INTO car_type VALUES ('Honda', 'Civic Sedan', 2009);
INSERT INTO car_type VALUES ('Honda', 'Civic Coupe', 2009);
INSERT INTO car_type VALUES ('Honda', 'Civic Hybrid Sedan', 2009);
INSERT INTO car_type VALUES ('Honda', 'Accord Sedan', 2009);
INSERT INTO car_type VALUES ('Honda', 'Accord Coupe', 2009);
INSERT INTO car_type VALUES ('Honda', 'Fit', 2009);
INSERT INTO car_type VALUES ('Honda', 'Fit Sport', 2009);
OR you can do a single insert + select like this...
Code:
INSERT INTO car_type SELECT make, model, 2009 FROM car_type WHERE make = 'Honda' AND year = 2008;
In this case, the '2009' in the SELECT behaves exactly like the '1' in the SELECT of the previous example. The only difference here is that we're
also getting other values in the SELECT and passing them to the INSERT.
In either case, we get...
Code:
make | model | year
-------+--------------------+------
Honda | Civic Sedan | 2008
Honda | Civic Coupe | 2008
Honda | Civic Hybrid Sedan | 2008
Honda | Accord Sedan | 2008
Honda | Accord Coupe | 2008
Honda | Fit | 2008
Honda | Fit Sport | 2008
Honda | Civic Sedan | 2009
Honda | Civic Coupe | 2009
Honda | Civic Hybrid Sedan | 2009
Honda | Accord Sedan | 2009
Honda | Accord Coupe | 2009
Honda | Fit | 2009
Honda | Fit Sport | 2009
Pretty neat, huh?