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

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

-- 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

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

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

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

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

  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

  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

  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

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

 

insert elements if not exists

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

-- 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

  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}=?
     )`