Random selection, with a bias ...

Say you want to randomly select your employee of the month, but not so randomly, better, you'd like to give your best employees a bigger chance to be selected based on their rating.
This is just an example, you could be randomly displaying ads from your customers, but giving an higher chance to be displayed to those who are paying more, there can be a million other example, but I hope you got the sense of this.
In other terms this means to add some skew to your data, so that it's distribution is no more uniform.
Apart from the statistical implications of this, let's see the sql in action, I'll be using Firebird's Employee sample database, first of all we'll add a column to the employee table, holding the employee rating, then set the rating to 5 for employee 2, 10 for employee 4, 15 for employee 5, 1 for all other employees.
Now that we have rated each employee we need the magic to have them randomly selected with a bias ...
This is accomplished with a "sequence table" which will do just one thing, hold a list of numbers starting from the minimum rating value to the max rating (you'll probably want to preload it with a lot of numbers starting from 1).
Here is the sql:

CREATE TABLE CONSECUTIVE_NUMBER(
NUM Integer NOT NULL,
CONSTRAINT PK_CONSECUTIVE_NUMBER_1 PRIMARY KEY (NUM)
);

And load it:

insert Into Consecutive_number (num) Values (1);
Insert Into Consecutive_number (num) Values (2);
Insert Into Consecutive_number (num) Values (3);
Insert Into Consecutive_number (num) Values (4);
Insert Into Consecutive_number (num) Values (5);
Insert Into Consecutive_number (num) Values (6);
Insert Into Consecutive_number (num) Values (7);
Insert Into Consecutive_number (num) Values (8);
Insert Into Consecutive_number (num) Values (9);
Insert Into Consecutive_number (num) Values (10);
Insert Into Consecutive_number (num) Values (11);
Insert Into Consecutive_number (num) Values (12);
Insert Into Consecutive_number (num) Values (13);
Insert Into Consecutive_number (num) Values (14);
Insert Into Consecutive_number (num) Values (15);
Insert Into Consecutive_number (num) Values (16);
Insert Into Consecutive_number (num) Values (17);
Insert Into Consecutive_number (num) Values (18);
Insert Into Consecutive_number (num) Values (19);
Insert Into Consecutive_number (num) Values (20);

Now we are ready for the magic, we'll be joining our employee table with this sequence table, generating a specific cross join which will hold as many rows for each employee as it's rating, thus, when doing a random select on this data there will be a bigger chance to choose some employees, i.e. employee number 5 will have 15 times the chance to be selected than employee number 8 and 3 times the chances of employee number 2.
Here is the query, embedded in a view:

CREATE VIEW
RANDOM_SKEWED_EMPLOYEE (EMP_NO)
AS
/* write select statement here */
SELECT
FIRST 1
a.EMP_NO
FROM
EMPLOYEE a
INNER JOIN
CONSECUTIVE_NUMBER c
ON
c.NUM BETWEEN 1 AND a.RATING
ORDER BY
rand();

The join clause does the magic, as I said.
Now extract a small sample:

24
5
36
85
5
2
12
24
144
5
5
20
94
5
4
127
5
94
85
4
2
34
136
4
9

Let's see it's distribution, even on such a small sample you'll see that those 3 employees appear more often than others and with more or less the expected relative ratio:

Emp_no Times
2 5
4 9
5 15
9 4
14 1
20 1
28 2
29 2
36 1
37 1
52 2
71 1
72 1
83 1
113 1
114 1
118 2
121 1
127 1
138 2
144 1

Of course a larger sample will be closer to the desired result.
This example is buit on Firebird, but it should work in any database.
HTH

Note that PostgreSQL's generate_series() instruction might help a lot ;)

Don't silently turn your outer joins into inner joins ...

Surprised by outer joins not returning the expected results? Looks like they behave like an inner join?

You are probably messing with the join and the where clause.

That's also why it's important to use the full join notation for every join instead of the where based one (often used for inner joins) as it allows for a better separation of the join and the where clause.

Remember that the where clause is applied after the join clause, so if you want to restrict results from the table on the "right" of the left outer join but still have the NULLs you'll have to put the restriction in the join condition, putting it in the where clause will turn your outer join into an inner join.

Not convinced? Let's see an example:

I'll query two tables of the Firebird sample database, EMPLOYEE and EMPLOYEE_PROJECT, I'm looking for a list of employees assigned to a specific project and those not assigned (probably I'll pick a few of them and assign those too), let's see the first query

SELECT 
e.EMP_NO,
e.FULL_NAME,
ep.PROJ_ID 
FROM 
 EMPLOYEE e LEFT OUTER JOIN EMPLOYEE_PROJECT ep
  ON e.EMP_NO = ep.EMP_NO; 

and it's output is

2 Nelson, Robert [null]
4 Young, Bruce VBASE
4 Young, Bruce MAPDB
5 Lambert, Kim [null]
8 Johnson, Leslie VBASE
8 Johnson, Leslie GUIDE
8 Johnson, Leslie MKTPR
9 Forest, Phil [null]
11 Weston, K. J. [null]
12 Lee, Terri MKTPR
14 Hall, Stewart MKTPR

So far so good, now I want only those on the VBASE project and the unassigned ones, let's do what instinct suggests, add a where clause, the outer join will take care of the rest (...)

SELECT 
 e.EMP_NO,
 e.FULL_NAME,
 ep.PROJ_ID 
FROM EMPLOYEE e LEFT OUTER JOIN EMPLOYEE_PROJECT ep
 ON e.EMP_NO = ep.EMP_NO
WHERE 
 ep.PROJ_ID = 'VBASE' 

Result is:

4 Young, Bruce VBASE
8 Johnson, Leslie VBASE
15 Young, Katherine VBASE
44 Phong, Leslie VBASE
45 Ramanathan, Ashok VBASE
71 Burbank, Jennifer M. VBASE
83 Bishop, Dana VBASE
136 Johnson, Scott VBASE
138 Green, T.J. VBASE
145 Guckenheimer, Mark VBASE

Eeek, where are all the unassigned gone???
That's because the WHERE is applied after the JOIN and so it throws away all those NULLs, infact it's bound to pick only PROJ_ID = 'VBASE'
Now the right one, we want it executed in the join

SELECT 
 e.EMP_NO,
 e.FULL_NAME,
 ep.PROJ_ID
FROM EMPLOYEE e LEFT OUTER JOIN EMPLOYEE_PROJECT ep
 ON e.EMP_NO = ep.EMP_NO
  AND 
   ep.PROJ_ID = 'VBASE'  

And the result is just what we are looking for:

2 Nelson, Robert [null]
4 Young, Bruce VBASE
5 Lambert, Kim [null]
8 Johnson, Leslie VBASE
9 Forest, Phil [null]
11 Weston, K. J. [null]
12 Lee, Terri [null]
14 Hall, Stewart [null]
15 Young, Katherine VBASE
20 Papadopoulos, Chris [null]
24 Fisher, Pete [null]
28 Bennet, Ann [null]
29 De Souza, Roger [null]

Integrated auth in Firebird 2.1

Just a quick sample of integrated windows auth in Firebird 2.1
Logon to my Firebird server as a user with administrative privileges on the machine:

C:\Programmi\Firebird\Firebird_2_1\bin>isql localhost:employee
Database:  localhost:employee
SQL>

So I'm logged. let's check who am I for Firebird:

SQL> select current_user from rdb$database;

USER

===============================================================================

SYSDBA


SQL>

That's right, as an administrator on the machine, I'm SYSDBA on the database, now let's try with an unprivileged user I have on this machine:
I'll open another command line client as user postgres (guess what other database is installed on this machine?)

C:\Programmi\Firebird\Firebird_2_1\bin>runas /user:postgres cmd
Immettere la password per postgres:
Tentativo di avvio di cmd come utente "AB-2346789223445\postgres" ...

C:\Programmi\Firebird\Firebird_2_1\bin>

Now I have another command line window open

C:\Programmi\Firebird\Firebird_2_1\bin>isql localhost:employee
Database:  localhost:employee
SQL> select current_user from rdb$database;

USER

===============================================================================

AB-2346789223445\POSTGRES


SQL>

Ok, I'm in as postgres, let's try a select

SQL> select * from country;

COUNTRY         CURRENCY
=============== ==========
USA             Dollar
England         Pound
Canada          CdnDlr
Switzerland     SFranc
Japan           Yen
Italy           Lira
France          FFranc
Germany         D-Mark
Australia       ADollar
Hong Kong       HKDollar
Netherlands     Guilder
Belgium         BFranc
Austria         Schilling
Fiji            FDollar

SQL>

This works as expected, table country is available for SYSDBA and for PUBLIC, my current user is in PUBLIC by default.
Let's check a different table, on which PUBLIC has no grants

SQL> select * from tbl_stock_warehouse;
Statement failed, SQLCODE = -551
no permission for read/select access to TABLE TBL_STOCK_WAREHOUSE
SQL>

My request has been rejected as expected, great!
If this is not enough for you a lot of improvements in the user management area are currently in the works for Firebird 2.5 (Core 696 and CORE 1660) and in derived databases like RedSoft's one which will hopefully be merged in the official Firebird server.

Simulating procedural logic

Sometimes I see people having great difficulties in describing how to fetch data for a report.
They are unable to reason by sets and tend to describe things in procedural terms.
Here I'm posting a small example of how you can write a query that reproduces that procedural reasoning and lets the optimizer do the work of translating it into efficient SQL.
Say someone has a table structure like this, a main table named guys holding their id and name and two tables bads_attributes and goods_attributes, if you are a bad guy your attributes will be in the bads_attributes table and vice versa.
Looks ugly? It is, but you'll find it around, sooner or later :-(
And it's not even the worst case scenario, confronted with a similar requirement I've heard that stored procedures where proposed as the ideal solution.
The table structure:

DROP TABLE IF EXISTS `test`.`guys`;
CREATE TABLE  `test`.`guys` (
`guy_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guy_name` varchar(45) NOT NULL,
PRIMARY KEY (`guy_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`bads_attributes`;
CREATE TABLE  `test`.`bads_attributes` (
`attribute_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guy_id` int(10) unsigned NOT NULL,
`attribute_name` varchar(45) NOT NULL,
PRIMARY KEY (`attribute_id`,`guy_id`),
KEY `FK_bads_attributes_1` (`guy_id`),
CONSTRAINT `FK_bads_attributes_1` FOREIGN KEY (`guy_id`) REFERENCES `guys` (`guy_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`goods_attributes`;
CREATE TABLE  `test`.`goods_attributes` (
`attribute_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guy_id` int(10) unsigned NOT NULL,
`attribute_name` varchar(45) NOT NULL,
PRIMARY KEY (`attribute_id`,`guy_id`),
KEY `FK_goods_attributes_1` (`guy_id`),
CONSTRAINT `FK_goods_attributes_1` FOREIGN KEY (`guy_id`) REFERENCES `guys` (`guy_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Now let's retrieve those attributes with a query that mimics the logic described above

SELECT
 g.guy_id,
 g.guy_name,
 CASE
  WHEN EXISTS (SELECT 0 FROM bads_attributes b WHERE b.guy_id = g.guy_id)
   THEN (SELECT group_concat(b.attribute_name separator ', ') FROM bads_attributes b WHERE b.guy_id = g.guy_id GROUP BY b.guy_id)
  WHEN EXISTS (SELECT 0 FROM goods_attributes a WHERE a.guy_id = g.guy_id)
   THEN (SELECT group_concat(a.attribute_name separator ', ') FROM goods_attributes a WHERE a.guy_id = g.guy_id GROUP BY a.guy_id)
  ELSE 'no attributes for this guy'
 END right_attributes
FROM
 guys g 

This goes after the reasoning described above, if you are found in the bads_attributes then your data is retrieved from there, the same for goods_attributes.
Output is

+--------+----------+----------------------------+
| guy_id | guy_name | right_attributes           |
+--------+----------+----------------------------+
|      1 | Paolo    | Fichissimo                 |
|      2 | Carlo    | Fico                       |
|      3 | Ciccio   | Ugly, Uglier               |
|      4 | Bender   | Ugliest                    |
|      5 | New kid  | no attributes for this guy |
+--------+----------+----------------------------+
5 rows in set (0.02 sec)

It's explain plan is

+----+--------------------+-------+------+-----------------------+-----------------------+---------+---------------+------+-------------+
| id | select_type        | table | type | possible_keys         | key         | key_len | ref           | rows | Extra       |
+----+--------------------+-------+------+-----------------------+-----------------------+---------+---------------+------+-------------+
|  1 | PRIMARY            | g     | ALL  | NULL                  | NULL         | NULL    | NULL          |    5 |             |
|  5 | DEPENDENT SUBQUERY | a     | ref  | FK_goods_attributes_1 | FK_goods_attributes_1 | 4       | test.g.guy_id |    1 | Using where |
|  4 | DEPENDENT SUBQUERY | a     | ref  | FK_goods_attributes_1 | FK_goods_attributes_1 | 4       | test.g.guy_id |    1 | Using index |
|  3 | DEPENDENT SUBQUERY | b     | ref  | FK_bads_attributes_1  | FK_bads_attributes_1  | 4       | test.g.guy_id |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | b     | ref  | FK_bads_attributes_1  | FK_bads_attributes_1  | 4       | test.g.guy_id |    1 | Using index |
+----+--------------------+-------+------+-----------------------+-----------------------+---------+---------------+------+-------------+
5 rows in set (0.00 sec)

Well, it could be worse, note that I'm using InnoDB tables and I've declared foreing keys, whose indexes are picked up by the optimizer.

Let's a more SQLish version of this query

SELECT
 g.guy_id,
 g.guy_name,
 COALESCE(
  group_concat(b.attribute_name separator ', '),
  group_concat(a.attribute_name separator ', ')
 ) right_attributes
FROM 
 guys g LEFT OUTER JOIN bads_attributes b 
  ON g.guy_id = b.guy_id
 LEFT OUTER JOIN goods_attributes a 
  ON g.guy_id = a.guy_id
GROUP BY
 g.guy_id,
 g.guy_name 

Output is the same

+--------+----------+---------------------------+
| guy_id | guy_name | right_attributes          |
+--------+----------+---------------------------+
|      1 | Paolo    | Fichissimo                |
|      2 | Carlo    | Fico                      |
|      3 | Ciccio   | Ugly, Uglier              |
|      4 | Bender   | Ugliest                   |
|      5 | New kid  | NULL                      |
+--------+----------+---------------------------+
5 rows in set (0.00 sec)

And the explain plan

+----+-------------+-------+------+-----------------------+-----------------------+---------+---------------+------+----------------+
| id | select_type | table | type | possible_keys         | key  | key_len | ref           | rows | Extra          |
+----+-------------+-------+------+-----------------------+-----------------------+---------+---------------+------+----------------+
|  1 | SIMPLE      | g     | ALL  | NULL                  | NULL  | NULL    | NULL          |    5 | Using filesort |
|  1 | SIMPLE      | b     | ref  | FK_bads_attributes_1  | FK_bads_attributes_1  | 4       | test.g.guy_id |    1 |                |
|  1 | SIMPLE      | a     | ref  | FK_goods_attributes_1 | FK_goods_attributes_1 | 4       | test.g.guy_id |    1 |                |
+----+-------------+-------+------+-----------------------+-----------------------+---------+---------------+------+----------------+
3 rows in set (0.00 sec)

Well it looks certainly better, but there's a nasty filesort (this is a very small data set) which should be checked against a much larger dataset.

Doing the same on Firebird 2.1 beta 2 (which supports LIST() a function similar to MySQL's group_concat()) leads to:

SELECT
 g.guy_id,
 g.guy_name,
 CASE
  WHEN EXISTS (SELECT 0 FROM bads_attributes b WHERE b.guy_id = g.guy_id)
   THEN (SELECT CAST(list(b.attribute_name) AS varchar(5000)) FROM bads_attributes b WHERE b.guy_id = g.guy_id 
GROUP BY b.guy_id
)
  WHEN EXISTS (SELECT 0 FROM goods_attributes a WHERE a.guy_id = g.guy_id)
   THEN (SELECT CAST(list(a.attribute_name) AS varchar(5000))  FROM goods_attributes a WHERE a.guy_id = g.guy_id 
GROUP BY a.guy_id
)
  ELSE 'no attributes for this guy'
 END right_attributes
FROM
 guys g 

and

SELECT
 g.guy_id,
 g.guy_name,
 CAST(
  COALESCE(
   list(b.attribute_name),
   list(a.attribute_name)
 ) AS varchar(5000)) right_attributes
FROM 
 guys g
LEFT OUTER JOIN bads_attributes b 
 ON g.guy_id = b.guy_id
LEFT OUTER JOIN goods_attributes a 
 ON g.guy_id = a.guy_id
GROUP BY
 g.guy_id,
 g.guy_name 

Note that both queries need an explicit cast as list's results in Firebird are blobs.
The respective explain plans show that the set oriented one is better.

Prepare time: 00:00:00.
Field #01: GUYS.GUY_ID Alias:GUY_ID Type:INTEGER
Field #02: GUYS.GUY_NAME Alias:GUY_NAME Type:STRING(45)
Field #03: . Alias:RIGHT_ATTRIBUTES Type:STRING(5000)
PLAN (B INDEX (FK_BADS_ATTRIBUTES_1))
PLAN (B ORDER FK_BADS_ATTRIBUTES_1 INDEX (FK_BADS_ATTRIBUTES_1))
PLAN (A INDEX (FK_GOODS_ATTRIBUTES_1))
PLAN (A ORDER FK_GOODS_ATTRIBUTES_1 INDEX (FK_GOODS_ATTRIBUTES_1))
PLAN (G NATURAL)


Executing...
Done.
116 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 21 index, 5 seq.
Delta memory: 54852 bytes.
Execute time: 00:00:00.

and

Prepare time: 00:00:00.
Field #01: GUYS.GUY_ID Alias:GUY_ID Type:INTEGER
Field #02: GUYS.GUY_NAME Alias:GUY_NAME Type:STRING(45)
Field #03: . Alias:RIGHT_ATTRIBUTES Type:STRING(5000)
PLAN JOIN (SORT (JOIN (G NATURAL, B INDEX (FK_BADS_ATTRIBUTES_1))), A INDEX (FK_GOODS_ATTRIBUTES_1))


Executing...
Done.
66 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 9 index, 5 seq.
Delta memory: 38168 bytes.
Execute time: 00:00:00.

Custom ordering for your results

... or, taming the ORDER BY clause.
Say you want to implement a custom ordering for your queries, as an example, you want to display each customer's orders with shipped orders first, then waiting orders and open orders last.
That's ordering by the 'status' column and you can't use alphabetical ordering!
In this case you'll have to implement some kind of logic in your order by clause, and a CASE is very handy, like this:

SELECT * FROM SALES
ORDER BY 
cust_no,
CASE WHEN order_status = 'shipped' THEN 1
WHEN order_status = 'waiting' THEN 2
WHEN order_status = 'open' THEN 3
ELSE 4
END; 

Note how the ordering is performed on the values substituted by the CASE statement and not
for the original column values.
This example is based on Firebird's EXAMPLE.FDB database.

The MySQL guys could also use a trick like the FIELD function

... ORDER BY FIELD(order_status,'shipped','waiting','open')

but this is a NON standard way of doing things that I don't recommend at all.

Of correlated and uncorrelated subqueries

Many SQL questions on Devshed forums are easily solved using correlated and uncorrelated subqueries, but many don't know of them at all, or at least are not shure about the syntax, usage and performance implications, they sometimes rave about a stored procedure to loop and select other values ...
The definition (taken from O'Reilly's "Learning SQL on SQL Server 2005") of a correlated subquery:
A correlated subquery is an inner subquery whose information is referenced by the main, outer query such that the inner query may be thought of as being executed repeatedly.
And the definition of an uncorrelated subquery:
A noncorrelated subquery is a subquery that is independent of the outer query. In other words, the subquery could be executed on its own.
This is all fine and dandy, but what's the use?
I'll put up two simple examples of usage, which should make clear which kind of questions can be answered with (un)correlated subqueries:

Example 1: get the number of "open" orders, the number of "waiting" orders and the open vs. waiting ratio

SELECT 
COUNT(*) AS o_open, 
(SELECT COUNT(*) FROM sales WHERE order_status = 'waiting') AS o_waiting,
(COUNT(*))/ 
(SELECT COUNT(*) FROM sales WHERE order_status = 'waiting') AS open_waiting_ratio
FROM SALES WHERE order_status = 'open'; 

You can see that the queries are uncorrelated, and they better be, as the where conditions are different.

Example 2: we want to see the customer number, the amount of the customer's last order and the total amount ordered by each customer

SELECT 
s.cust_no,
s.total_value AS order_value,
(SELECT SUM(i_s.total_value) FROM sales i_s WHERE i_s.cust_no = s.cust_no) total_ordered
FROM sales s
WHERE
s.order_date =
(SELECT MAX(ii_s.order_date) FROM sales ii_s WHERE ii_s.cust_no = s.cust_no); 

You can see that both subqueries are correlated, the first one gets total amount ordered for each customer, the second one gets the last order date (field is datetime) and helps determine the last order in the main query.

This should make you aware that some kind of set based logic is often disguised as procedural and usually leads to ugly cursor usage in stored procedures instead of direct queries by the inexperienced.

Another example of a correlated subquery to display customers and one randomly selected purchase order for each of them:

SELECT 
 a.CUST_NO, 
 a.CUSTOMER,
 b.PO_NUMBER
FROM 
 CUSTOMER a
  INNER JOIN
   sales b
    ON a.CUST_NO = b.CUST_NO
WHERE 
 b.PO_NUMBER = 
(
  SELECT 
   first 1 c.PO_NUMBER 
  FROM 
   sales c 
  WHERE 
   c.CUST_NO = b.CUST_NO 
  ORDER BY 
   rand()
) 

Both examples are run against Firebird's standard EMPLOYEE.FDB.


Correlated subqueries are also useful to specify updates and deletes on a table conditioned to values of another table (instead of joining them) see this example:

UPDATE 
 stops s
SET 
 s.inservice=1 
WHERE EXISTS
(
 SELECT
  ls.stopid
 FROM 
  linestop ls
 WHERE
  s.stopid=ls.stopid 
 AND 
  ls.signid=79
);