SQL notes

** SQL Query Optimization: Implicit Join with UPDATE

When working with SQL UPDATE statements, particularly when joining tables, it is common to perform a join to update one table based on data from another. In PostgreSQL, there are two main ways to write such a query: using an implicit join in the FROM clause or an explicit JOIN syntax. Both approaches achieve the same result, but the implicit join syntax is a shorthand version of the explicit JOIN approach.

** Example with Implicit Join (Shortened Syntax)

UPDATE target_table SET target_column = target_column + 1 FROM source_table WHERE source_table.related_column IS NOT NULL AND source_table.related_column = target_table.key_column;

** Equivalent Example with Explicit JOIN

UPDATE target_table SET target_column = target_column + 1 FROM source_table JOIN target_table AS t ON source_table.related_column = t.key_column WHERE source_table.related_column IS NOT NULL;

** Explanation:

In both queries, the goal is to update the target_table by incrementing the target_column based on matching rows from the source_table. The first query uses the FROM clause to introduce the source_table and the WHERE clause to specify the join condition. PostgreSQL understands this condition as a join, and it will match rows between target_table and source_table where source_table.related_column matches target_table.key_column. The query then updates the target_column in target_table by incrementing its value for each matched row.

In the second query, we explicitly use the JOIN syntax to indicate how the tables should be joined (on source_table.related_column = target_table.key_column). The JOIN is followed by the condition in the WHERE clause to ensure that only rows with a non-null related_column in the source_table are considered for the update.

Both versions are logically equivalent. PostgreSQL optimizes both queries using efficient join algorithms (such as hash or merge joins), which means that the performance of both versions should be similar. The first query is considered a shorthand or a more concise version of the second query. Although the JOIN syntax is more explicit, PostgreSQL internally treats the FROM/WHERE approach as a form of implicit join.

** How SQL UPDATE with JOIN Matches Rows in the Update Table and Result Set

For example: 

UPDATE employees
SET salary = salary + employee_bonuses.bonus_amount
FROM employees
JOIN employee_bonuses ON employees.id = employee_bonuses.employee_id;

- A row in the update table (employees) is updated if it appears in the result set produced by the JOIN.
- The result set is formed by combining rows from both the employees table and the employee_bonuses table, based on the JOIN condition (employees.id = employee_bonuses.employee_id).
- For each row in the result set that corresponds to a row in the update table, the UPDATE operation is applied to the update table’s row.
- Multiple rows in the result set can cause multiple updates to the same row in the update table if that row matches multiple rows in the result set (i.e., when one employee has multiple matching bonus records).

I wipe my .sqlite browser session caches often as to not leave a solid trace or past history of my web browser activities. You can manually program Bleachbit or Ccleaner to wipe all the browser session sqlite data. If you are using Windows 7 or newer you can find them in your C:\Users account %AppData% /Roaming/ (a default hidden directory, just allow the OS to show hidden directories in "folder settings" and you'll find it).

**Web browser caches:**
formhistory.sqlite
cookies.sqlite
cookies.sqlite-shm
cookies.sqlite-wal
favicons.sqlite
favicons.sqlite-shm
favicons.sqlite-wal
formhistory.sqlite
places.sqlite
places.sqlite-shm
places.sqlite-wal
protections.sqlite
webappsstore.sqlite
SiteSecurityServiceState.txt
enumerate_devices.txt
broadcast-listeners.json
sessionCheckpoints.json
times.json

**Browser directory caches:**
...\datareporting\|*.*
...\sessionstore-backups\|*.*
...\storage\private\|*.*
...\startupCache\|*.*

NOTE: this will be inconvenient for most users, especially normies who cannot remember their online accounts or passwords and who rely on browser session data to log into accounts. Do not try this unless you want maximum privacy when it comes to your web browser history. This is kinda a tin foil option but I am used to it.

**HAVING Clause vs. WHERE Clause

-- WHERE filters rows before aggregation happens. It works on individual rows in the database.

-- HAVING filters rows after aggregation, meaning it works on the results of the GROUP BY clause.