Joshua's Docs - SQL Notes and Tricks
Light
help

Comments

You can use a double dash with spacing around it:

SELECT * FROM Users WHERE Users.isAdmin = true -- Get admin users

Or, typical block comment type:

/* Get admin users */
SELECT * FROM Users WHERE Users.isAdmin = true

Aliases

/* Combine selecting all PLUS an alias in one query */
	/* Actually should work without anything special */
	SELECT *, AVG(MY_COLUMN) as 'average' FROM MY_TABLE WHERE [MY_FILTER]
	/* Can also prefix to be careful */
	SELECT MY_TABLE.*, AVG(MY_TABLE.MY_COLUMN) as 'average' FROM MY_TABLE WHERE [MY_FILTER]
	
/* You can alias entire tables to make queries shorter */
	/* something like... */
	SELECT s.id, s.name, s.dob, s.gender, s.enroll_date, s.phone, s.curr_level, g.gpa
	FROM student_records AS s, student_grades as g
	LEFT JOIN student_grades
	ON s.id=g.student_id
	/* You can also alias within a function like join, like so: */
	SELECT s.id, s.name, s.dob, s.gender, s.enroll_date, s.phone, s.curr_level, g.gpa
	FROM student_records AS s
	LEFT JOIN student_grades as g
	ON s.id=g.student_id

Tricks

Fake constants

/* Inserting a fake constant that you can use to join with, etc. */
/* --- > As easy as simply putting "[VALUE] AS [COLUMN_NAME]"

	/* Adding a dummy int */
	SELECT id, 1 AS dummyint FROM records

	/* or a string */
	select id, 'foobar' AS dummystring FROM records

/* Using fake constant to join on unrelated aggregate columns */
	SELECT uploads, downloads FROM
	((SELECT count(id) AS "uploads",1 as dummy FROM stats WHERE stats.category = "image-host" AND stats.action = "upload") U
	LEFT JOIN
	(SELECT count(id) AS "downloads",1 as dummy FROM stats WHERE stats.category = "image-host" AND stats.action = "download") D
	ON U.dummy = D.dummy)

Joins

SELECT students.*, contact_entries.email
FROM students
LEFT JOIN contact_entries
ON students.contact_id = contact_entries.id

Join with subquery

SELECT *
FROM orders
LEFT JOIN (
		SELECT customer_id, SUM(order_total) AS past_spend
		FROM orders
		WHERE fulfilled = 1
		GROUP BY customer_id
) AS past_orders_summed
ON orders.customer_id = past_orders_summed.customer_id
WHERE orders.fulfilled = 0

Join, keep all columns of original table, and only certain columns of joined

In your select statement, use:

SELECT origin_table.*, joined_table.my_col

Multiple ON

You can use multiple ON conditions with a join - just use parenthesis for grouping:

LEFT JOIN ___
ON (___ AND ___)

Related S/O

Markdown Source Last Updated:
Thu Sep 03 2020 02:25:03 GMT+0000 (Coordinated Universal Time)
Markdown Source Created:
Wed Sep 25 2019 12:16:45 GMT+0000 (Coordinated Universal Time)
© 2020 Joshua Tzucker, Built with Gatsby
Feedback