Join or Subquery? Cheatsheets for MySQL

http://www.artfulsoftware.com/infotree/mysqlquerytree.php

Join or subquery?

Usually, a JOIN is faster than an uncorrelated subquery. For example in the sakila test database, customer is a parent of rental (via customer_id) which in turn is a parent of payment (via rental_id). The subquery version of a query for whether a customer has made payments and rentals... 

SELECT DISTINCT c.customer_id
FROM customer c
WHERE c.customer_id IN (
   SELECT r.customer_id 
   FROM rental r
   JOIN payment p USING (rental_id) 
   WHERE c.customer_id = 599;
); 
is eight times slower than the join version...
 
SELECT DISTINCT c.customer_id
FROM customer c
JOIN rental r USING (customer_id)
JOIN payment p USING (rental_id) 
WHERE c.customer_id = 599; 
Running EXPLAIN on the two queries reveals why: the subquery version has to read most customer rows, while the join version proceeds inside out and discovers it needs to read just one customer row.



http://www.databasejournal.com/features/mysql/article.php/3904531/The-10-Most-Common-MySQL-Queries.htm

http://www.addedbytes.com/cheat-sheets/mysql-cheat-sheet/

http://www.web-design-talk.co.uk/128/mysql-cheatsheet-useful-mysql-queries/

MySQL Cheatsheet – Useful MySQL Queries

What follows is a list of some very useful MySQL queries for use in projects, enjoy. I won’t go into detail how and why they work, I’ll leave that up to you 

01	/*** select records from the previous day ***/

02	SELECT * FROM users WHERE TO_DAYS(last_login) = ( TO_DAYS(NOW()) - 1 )

03

04	/*** select records from last 90 minutes ***/

05	SELECT* FROM users DATE_SUB(NOW(),INTERVAL 90 MINUTE);

06

07	/*** select records from last 1hr 5 mins ***/

08	SELECT DATE_ADD('1970-01-01 12:00:00', INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE);

09

10	/*** select records from last hour ***/

11	select DATE_SUB(NOW(), INTERVAL 1 HOUR);

12

13	/*** using the SIGN function to mark a number as positive, negative or null ***/

14	SELECT backlist, SIGN(backlist) AS user_to_backlist

15	FROM users

16	WHERE user_banned IS NOT NULL;

17

18	/*** select records from last week ***/

19	select DATE_SUB(NOW(), INTERVAL 1 WEEK);

20

21	/*** get the last day of next month ***/

22	SELECT LAST_DAY('2006-03-06' + INTERVAL 1 MONTH) AS last_day;

23

24	/*** select unique records only ***/

25	SELECT user_name FROM users GROUP BY users HAVING ( COUNT(user_name) = 1 );

26

27	/*** select records from one table that are in another table i.e. all the customers that have placed an order ***/

28	SELECT DISTINCT cust.customer_id, cust.customer_name

29	FROM cust INNER JOIN orders ON cust.customer_id = orders.customer_id;

30

31	/*** insert data from one table into another ***/

32	INSERT INTO customers(customer_id, customer_name)

33	SELECT cus_key, cus_name

34	FROM customers_2 WHERE customer_name LIKE 'W%';

35

36	/*** update information based upon a seperate table ***/

37	UPDATE cust SET status = '1'

38	FROM orders WHERE orderdate > '2009-01-01' and orders.customer_id = cus.customer_id;

39

40	/*** classic self join example - who is an emoployees manager ***/

41	SELECT emp.empID, emp.Name, emp.Salary, managers.Name AS manager_name

42

FROM emp

43	LEFT JOIN emp AS manager_name

44	ON emp.ManagerID = Manager.EmployeeID

45	WHERE (emp.empID= '123456');

46

47	/*** using UNION to combine results from multiple queries into a single table ***/

48	SELECT users.name

49	FROM users WHERE (users.name BETWEEN 'A%' AND 'M%')

50

UNION

51	SELECT banned_users.name FROM banned_users

52	WHERE (banned_users.name BETWEEN 'A%' AND 'M%');

53

54	/*** concatenate column data into a single column ***/

55	SELECT CONCAT(emp.firstname, '-', emp.lastname) AS emp_full_name FROM emp;

56

57	/*** select count of records for each hour ***/

58	SELECT HOUR(last_login) AS last_login_hour, COUNT(*) AS the_count FROM users GROUP BY HOUR(last_login);

59

60	/*** import a csv file ***/

61	LOAD DATA INFILE '/path/xxx.csv' INTO users_table csv_test_table FIELDS TERMINATED BY ',' LINES TERMINATED BY "\n" (user_name, access_level , user_email);

62

63	/*** display current mysql user ***/

64	SELECT USER();

MySQL mysql query sample mysql queries