joplin-notes/joplin notion extension/Web Development/sql/SQL.md

SQL

https://learnsql.com/blog/sql-basics-cheat-sheet/ 

## Joins

**Example**
we have 2 tables named 'first' and 'join'
first table
|id|name|
|--|----|
|1|name1|
|2|name2|
|3|name3|

Books table
|id|user_id|book_name|
|--|-------|---------|
|1|1|book1|
|2|1|book2|
|3|1|book3|
|4|2|book4|
|5|4|book5|

**Cross product** (first X books)
Query
```sql
select * from first,Books;
```

Output
|id|name|id|user_id|book_name|
|--|----|--|-------|---------|
|1|name1|1|1|book1|
|2|name2|1|1|book1|
|3|name3|1|1|book1|
|1|name1|2|1|book2|
|2|name2|2|1|book2|
|3|name3|2|1|book2|
|1|name1|3|1|book3|
|2|name2|3|1|book3|
|3|name3|3|1|book3|
|1|name1|4|2|book4|
|2|name2|4|2|book4|
|3|name3|4|2|book4|
|1|name1|5|4|book5|
|2|name2|5|4|book5|
|3|name3|5|4|book5|

 

**Union** ( full outer join ) (first U Books)
Query
```sql
-- for mysql
SELECT *
FROM first
LEFT JOIN Books ON first.id = Books.user_id
UNION
SELECT *
FROM first
RIGHT JOIN Books ON first.id = Books.user_id;

-- for others
select * from first	 FULL JOIN Books
ON first.id = Books.user_id ;
```
Output
|id|name|id|user_id|book_name|
|--|----|--|-------|---------|
|1|name1|1|1|book1|
|1|name1|2|1|book2|
|1|name1|3|1|book3|
|2|name2|4|2|book4|
|3|name3||||
|||5|4|book5|

 

**Intersection** (inner join) (first ∩ Books)
Query
```sql
select * from first,Books
where first.id = Books.user_id;

-- or

select * from first JOIN Books
ON first.id = Books.user_id ;
```
Output
|id|name|id|user_id|book_name|
|--|----|--|-------|---------|
|1|name1|1|1|book1|
|1|name1|2|1|book2|
|1|name1|3|1|book3|
|2|name2|4|2|book4|


 

**LEFT JOIN** (first U first ∩ Books )
Means all of left table and intersection of 2 tables is also included
Query
```sql
SELECT *
FROM first
LEFT JOIN Books ON first.id = Books.user_id

-- or 

-- Select all rows from table1 and matching rows from table2
SELECT first.*, Books.*
FROM first, Books
WHERE first.id = Books.user_id
UNION ALL
-- Select all rows from table1 that have no matching row in table2
SELECT first.*, NULL AS id, NULL AS user_id , null as book_name  
FROM first
WHERE first.id NOT IN (SELECT user_id FROM Books);
```
Output
|id|name|id|user_id|book_name|
|--|----|--|-------|---------|
|1|name1|1|1|book1|
|1|name1|2|1|book2|
|1|name1|3|1|book3|
|2|name2|4|2|book4|
|3|name3||||

 

**RIGHT JOIN** (first ∩ Books U Books)
Means all of right table and intersection of 2 tables is also included
Query
```sql
SELECT *
FROM first
RIGHT JOIN Books ON first.id = Books.user_id;
```
Output
|id|name|id|user_id|book_name|
|--|----|--|-------|---------|
|1|name1|1|1|book1|
|1|name1|2|1|book2|
|1|name1|3|1|book3|
|2|name2|4|2|book4|
|||5|4|book5|

 

Triple JOIN
Query
```sql
SELECT h.Address, p.Name, p.Address as OwnerAddress, i.url FROM house AS h
INNER JOIN person AS p ON p.personID = h.personID
INNER JOIN images AS i ON i.personID = p.personID
GROUP BY h.houseID

```

 

**Some snippets**
- query to select those records in which user has login and there session is recorded. Not all users who loginned have their session recorded. 
  Table named `users` have columns `id` as int, `email`
  Table named `login` have columns like `id` and `email`
  Table named `sessions` have columns like `id` and  `userid` as text
  Connection between users and login is email and connection between users and sessions is userid. So we need to join these 3 tables as in order to connect `login` and `sessions` we need to connect it with mutual relationship `users`
  ```sql
  select  login.*, sessions.userid from users join login  on users.email = login.email
  join sessions on CAST(users.id as text) = sessions.userid
  group  by login.id, sessions.userid 
  ```
  As sessions.userid is text so we type cased users.id to text when comparing.
  
 ## Keys
 
 - Adding foriegn key
   https://www.w3schools.com/sql/sql_foreignkey.asp
   ```sql
   CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
	); 
   ```
   here Persons in the other table where Orders.PersonID is referencing to Persons.PersonID
   Make sure that foreign key dont contain value which is not available in reference table.
  - preventing delete related records for foreign keys
    Cascade Delete: This will automatically delete records in table B when the corresponding record in table A is deleted. `ON DELETE CASCADE`
	```sql
	CREATE TABLE A (
		id INT PRIMARY KEY,
		-- other fields
	);

	CREATE TABLE B (
		id INT PRIMARY KEY,
		a_id INT,
		-- other fields,
		FOREIGN KEY (a_id) REFERENCES A(id) ON DELETE CASCADE
	);

	```
    Set Null: This sets the foreign key in table B to NULL when the referenced record in table A is deleted.
	`ON DELETE SET NULL`
	Restrict: This will prevent the deletion of a record in table A if there are any related records in table B.
	`ON DELETE RESTRICT`. This is default
 
- cloning table data or structure
  - cloning structure only
    `CREATE TABLE employees_clone LIKE employees;`
  - cloning structure and data
    `CREATE TABLE employees_clone AS SELECT * FROM employees;`
 

Update command
```sql
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
```
	
 

insert elements if not exists
```sql
INSERT INTO table_name (column1, column2, column3)
SELECT 'value1', 'value2', 'value3'
WHERE NOT EXISTS (
    SELECT 1
    FROM table_name
    WHERE column1 = 'value1' AND column2 = 'value2'
);
```
 

update joins
https://stackoverflow.com/questions/982919/sql-update-query-using-joins
```sql
-- Source - https://stackoverflow.com/a/982947
-- Posted by gbn, modified by community. See post 'Timeline' for change history
-- Retrieved 2026-06-18, License - CC BY-SA 3.0

UPDATE im
SET mf_item_number = gm.SKU --etc
FROM item_master im
JOIN group_master gm
    ON im.sku = gm.sku 
JOIN Manufacturer_Master mm
    ON gm.ManufacturerID = mm.ManufacturerID
WHERE im.mf_item_number like 'STA%' AND
      gm.manufacturerID = 34

```
if this dont work then using legacy way
```js
  const updateQuery = `update ${songDataTableEnums.tablename} as sd 
     set ${songDataTableEnums.columns.is_bookmarked}=${bookmark_value} 
     where exists (
     select 1 
      from  ${keywordTableEnums.tablename} as k
      where sd.${songDataTableEnums.columns.id} = k.${keywordTableEnums.columns.song_id}
      and k.${keywordTableEnums.columns.keywords}=?
     )`
```