I just love SQL. I’ve been writing web-applications on top of relational databases (RDBMS) for a while; and, SQL always feels like a luxurious combination of structure and flexibility. Yesterday, when working with some data scientists, I had to merge a few columns from a database table into a CSV (Comma-Separated Values) file that the data scientists had created. The CSV file was fairly small; so, I ended up using the awesome power of multiple-cursors in SublimeText 3 in order to write a SQL query that converted the CSV data into a derived table that I then consumed in an INNER JOIN in MySQL.

To be clear, this is not the only way to consume CSV data in a relational database. But, I was working with a database in which I had read only permissions. As such, I couldn’t load the CSV file into a “real table”. Given the relatively small size of the CSV payload, what follows just felt like the appropriate level of effort.

That said, let’s paint a picture of what was going on. Imagine that I had a database table called friend with the following structure:

SHOW CREATE TABLE friend;
-- Generates the following SQL statement:

CREATE TABLE `friend` (
	`id` int(11) NOT NULL,
	`name` varchar(50) NOT NULL,
	`age` int(11) NOT NULL,
	`catchPhrase` varchar(250) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

#sql #mysql