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 v 
  SET price = s.price_per_vehicle  
  FROM shipments_shipment AS s 
  WHERE v.shipment_id = s.id

Two or more tables joined:

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

The standard SQL is not specific about this syntax, but for Postgres, for example, it works just fine.  For MySQL, the syntax is as follows:

UPDATE vehicle
INNER JOIN driver ON driver.vehicle_id = driver.id 
SET color = "blue";