Monday, April 20, 2015

SQL - Write Universal Querries

This post is supposed to deal with pecularities of SQL, and not one or another SQL dialect but the universal SQL, that can be ported without doing any porting to any other SQL database.


Boolean in Where-clause

If table client has a boolean column active, you may be tempted to write a query:

select * from client where active

However, to be sure it works on all databases, you better write it like this (for example, there are many other options):

select * from client where active = true

And, remember about nulls.  In general, every condition involving a variable which is not set (null) will be considered as a unsatisfied condition.

INSERT from SELECT

Sometimes, it's convenient to save a copy results of a select into the same or different table. No problem.  Here's how you do this:

INSERT INTO users
  (SELECT * FROM users1 WHERE age < 18)


Or, if you want just selected columns:

INSERT INTO users (id, age) 
  (SELECT id, age FROM users1 WHERE age < 18)

CREATE from SELECT (option: as temp table)

To create a table from select do:

CREATE [temp] table table_name as
SELECT t.name, t.id as my_id

FROM orig_table t
WHERE ...
With the "temp", the table will be auto deleted when session is killed.

I'm not sure this is standard SQL, but for Postgres for example it works just fine.

UPDATE with A JOIN

Joins can be done with an update, but the first table joined is joined the old way (link) and the next tables can be joined the new way:

The first table:

UPDATE vehicles_vehicle AS
  SET price = s.price_per_vehicle  
  FROM shipments_shipment AS
  WHERE v.shipment_id = s.id

Two or more tables joined:

UPDATE vehicles_vehicle AS
  SET price = s.price_per_vehicle  
  FROM shipments_shipment AS
  JOIN vehicles_owner o ON o.id = v.owner_id
  WHERE v.shipment_id = s.id

I'm not sure this is standard SQL, but for Postgres for example it works just fine.

No comments: