'SQL' Tag

  • SQL mini hack of the day, inverted IN clause

    November 13, 2015

    We are used to issue queries with an IN clause of the form: However I've had a few cases where I used an inverted format. Here's one use case followed by an inverted IN clause. Dynamic query building Say we have this function: Which, based on whether given clusterName is empty or not, would return […]

  • Leader election using MySQL

    October 14, 2015

    Being a stateful, centralized datastore, MySQL can serve in negotiating leadership: a mechanism to elect a single service out of multiple services; moreover, a mechanism to promote a new leader should the existing leader cease to function. What of Zookeeper? Zookeeper makes for an excellent leader election mechanism. This is one of the most recognized uses for […]

  • Tool of the day: q

    August 8, 2013

    If you work with command line and know your SQL, q is a great tool to use: q allows you to query your text files or standard input with SQL. You can: SELECT c1, COUNT(*) FROM /home/shlomi/tmp/my_file.csv GROUP BY c1 And you can: SELECT all.c2 FROM /tmp/all_engines.txt AS all LEFT JOIN /tmp/innodb_engines.txt AS inno USING […]

  • Merging tables with INSERT...ON DUPLICATE KEY UPDATE

    February 21, 2013

    Had a case recently where I had to merge data from two identically structured tables containing nearly identical data. "Nearly identical" meaning most table data is identical in both; sometimes a row is missing from one of the tables; sometimes same row (according to PK) appears in both, but some columns are NULL is one […]

  • Hierarchical data in INFORMATION_SCHEMA and derivatives

    January 8, 2013

    Just how often do you encounter hierarchical data? Consider a table with some parent-child relation, like the this classic employee table: CREATE TABLE employee (   employee_id INT UNSIGNED PRIMARY KEY,   employee_name VARCHAR(100),   manager_id INT UNSIGNED,   CONSTRAINT `employee_manager_fk` FOREIGN KEY (manager_id) REFERENCES employee (employee_id) ) engine=innodb ; +-------------+---------------+------------+ | employee_id | employee_name […]

  • Pop quiz: funny syntax

    November 5, 2012

    The following questions are of little importance, yet I find them entertaining. I stumbled upon these while developing QueryScript. Can you guess the results of the following statements? Pop quiz 1 SET @x := 7; SELECT ++@x; What is the computation result? What will be the value of @x? Pop quiz 2 SET @ := […]

  • Three wishes for a new year

    September 16, 2012

    Another new year by Jewish calendar. What do I wish for the following year? World peace Good health to all Get a decent, long waited for, implementation of Window Functions (aka Analytic Functions) for MySQL. I mean, I like GROUP_CONCAT, and the many hacks it provides: [1], [2], [3], [4], [5], [6], [7], [8], [9], […]

  • SQL: selecting top N records per group, another solution

    August 21, 2012

    A while back I presented SQL: selecting top N records per group, a "give me the top 5 countries in each continent" type of query, and which used an external numbers table and a lot of tedious casting. Here's another solution I came up with (*). Still using GROUP_CONCAT (how else?), but no external table […]

  • MySQL joins: ON vs. USING vs. Theta-style

    July 11, 2012

    What is the difference between the following three syntaxes? SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) SELECT * FROM film JOIN film_actor USING (film_id) SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id The difference is mostly syntactic sugar, but with a couple interesting notes. To put names, the first two are […]

  • Auto caching tables

    March 6, 2012

    Is there a way to create a caching table, some sort of a materialized view, such that upon selecting from that table, its data is validated/invalidated? Hint: yes. But to elaborate the point: say I have some table data_table. Can I rewrite all my queries which access data_table to read from some autocache_data_table, but have […]

Powered by Wordpress and MySQL. Theme by openark.org