RGB Venn Diagram

If you’re unfamiliar with the RGB color space, it works basically like this. You add a bit of a color component by increasing one of the values. R=0, G=0, B=0 (or 0,0,0) is black, and R=15, G=15, B=15 (or 15,15,15) is white. The examples below are all created using the data table format included below.

UNION

Union takes two (or more) query results and provides you with the unique result. For example, if I had two queries 3, queries that each returned a segment of the color set with the color value = 15 and the other color values > 14, I could write this as three select statements with a form similar to the following, which selects the red subset SELECT r, g, b FROM colors WHERE r = 15 AND g >= 14 AND b >= 14. That gives me 4 rows, Set 1 below.

Now I’ll include another collection of colors: SELECT r, g, b FROM colors WHERE r BETWEEN 10 AND 12 AND g = 11 AND b BETWEEN 12 AND 14, labeled Set 2. And one more: SELECT r, g, b FROM colors WHERE r BETWEEN 9 AND 10 AND g >= 14 AND b BETWEEN 14 AND 16 The result is Set 3.

And now all merged together using a UNION. Notice it removes the duplicate, highlighted in red above, saving us some time! The order by is just to make life easier when reading the newly merged results. As you can imagine, with a more complex dataset, this could be really handy!

SELECT r, g, b FROM colors WHERE r = 15 AND g >= 14 AND b >= 14
UNION
SELECT r, g, b FROM colors WHERE r BETWEEN 10 AND 12 AND g = 11 AND b BETWEEN 12 AND 14
UNION
SELECT r, g, b FROM colors WHERE r BETWEEN 9 AND 10 AND g = 11 AND b BETWEEN 14 AND 15
ORDER BY r,g,b

UNION ALL

As a quick example, using the same 3 queries and result sets from above, UNION ALL gives us the duplicate record.

SELECT r, g, b FROM colors WHERE r = 15 AND g >= 14 AND b >= 14
UNION ALL
SELECT r, g, b FROM colors WHERE r BETWEEN 10 AND 12 AND g = 11 AND b BETWEEN 12 AND 14
UNION ALL
SELECT r, g, b FROM colors WHERE r BETWEEN 9 AND 10 AND g = 11 AND b BETWEEN 14 AND 15
ORDER BY r,g,b

Tables

A table “colors” was created and into it I populated the full set of colors leveraging the ordinal int values of 0-15, resulting in 4,096 rows of colors. You can of course do this for all 256 values of RGB supported in the CSS color space, which would be more accurate for performance testing of your queries, but I digress…

CREATE TABLE `test`.`colors` (
  `r` INT(4),
  `g` INT(4),
  `b` INT(4),
  PRIMARY KEY (`r`, `g`, `b`)
)
CHARACTER SET utf8;

You can use this script to quickly populate your newly created table. There are lots of other ways to do it, but this was the fastest for me to write today.

$conn = mysqli_connect($server,$username,$password,$schema);
for($r=0; $r<16; $r++){
	for($g=0; $g<16; $g++){
		for($b=0;$b<16; $b++){
			$conn->query("INSERT INTO colors (r,g,b) VALUES ($r,$g,$b)");
		}
	}
}
$conn->close();

Tags: color, database, sql, union, union all, what works

This entry was posted on Tuesday, October 14th, 2008 at 9:37 am and is filed under software, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.