Tag finder: Tags... amplifier application bd blog book computing dailymotion debian dev electronic forum friends geek git github javascript jquery linux net nodejs ooc php pomm postgresql python screen shell silex speaker store symfony ubuntu valve vim whitepaper youtube

The power of VALUES

VALUES is a very useful SQL statement that can make very efficient queries. In fact, as soon as you write a bit of SQL, you use VALUES ... in the INSERT statement.

This example is very interesting to understand how VALUES can help. In fact, INSERT INTO can more generally look like this :

INSERT INTO my_table (field1, field2, ... fieldN) SELECT ... 

In fact, INSERT INTO ... VALUES is a special form of INSERT where VALUES allow you to specify a result set by hand. What advantages can we take on using this ? Let’s start with the example of one of my previous articles about window functions with the production table. Let’s say we want to query all the Chinese companies with a production greater or equal to 12, we would write the following query:

SELECT 
  p.* 
FROM 
  production p
WHERE 
    p.country='chine' 
  AND 
    p.production >= 12
;
 company_name | country | production 
--------------+---------+------------
 chang intl   | chine   |         15
 medong       | chine   |         12
(2 rows)

Using VALUES, it is possible to create a parameter like query:

SELECT 
  p.* FROM production p,
  (VALUES ('chine', 12)) data (country, production) 
WHERE 
    p.country=data.country 
  AND 
    p.production >= data.production
;
 company_name | country | production 
--------------+---------+------------
 chang intl   | chine   |         15
 medong       | chine   |         12
(2 rows)

Well, this query is longer, so what is the advantage of using VALUES here ? Well imagine now you have a matrix of requirements. You need to dump all US companies with a production >= 10, Chinese >=12, German > 5 and so on. The naive implementation would be to stack heaps of AND / OR statements but VALUES can be very powerful:

SELECT 
  p.* FROM production p,
  (VALUES 
    ('chine', 12),
    ('états-unis', 10),
    ('allemagne', 5)
  ) data (country, production) 
WHERE 
    p.country = data.country 
  AND 
    p.production >= data.production
;
 company_name |  country   | production 
--------------+------------+------------
 chang intl   | chine      |         15
 eep techno   | états-unis |         14
 futur corp   | états-unis |         12
 Glaß gmbh    | allemagne  |          8
 medong       | chine      |         12
(5 rows)

Enjoy !

Comments

Comments are using Textile restricted Markup language. Enjoy !

Be the first to comment this post.