learning sql

JOINS

![[f02mehra.bmp]]

FROM multiple tables

SELECT * FROM t1,t2
this shows all possible combinations of t1's columns with t2's columns.
this expression is often followed by a WHERE clause that filters out non real relations that were added
WHERE t1.id=t2.otherId
from there I can add more logical operators to do mo specific filtering

The philosophy of this approach is to first get a big table (that includes even non real relations) and work on filtering everything out until desired result.

nested queries

I can use () to make an expression/query inside a query
ex: SELECT name FROM Employee e1
WHERE salary > (SELECT salary FROM Employee where id=e1.managerId)

renaming columns

SELECT name as Employee
or SELECT name 'Employee'

group by aggregate functions

Select name, date
From A
Group by A.id
HAVING MIN date

Or the same without the last line and MIN(date) on line 1

inline arithmetic operators

I tried doing LIMIT 1 OFFSET N+1 and it throws syntax error.
seems like MySQL doesn't do inline arithmetic operations
I had to do

SET N = N+1 outside of the return statement

USING

USING keyword is syntax sugar for joining tabls w same column names
ON(film.film_id = actor.film_id) turns into USING(film_id)
Also when selecting * using doesnt return 2 cols w same name/duplicates

ranking functions

• Session variable
• Rank OVER()
  • always assigns rank based of a ORDER BY clause
  • always start at 1
  • dense_rank, rank and percent_rank
• Row_number()

•   	SELECT e.Salary, e.DepartmentId, (
          SELECT COUNT(DISTINCT(e1.Salary)) 
          FROM Employee e1 
          WHERE e1.Salary >= e.Salary 
          AND e.DepartmentId = e1.DepartmentId) AS "Rank"
     FROM Employee e
    	```
  
  

select nth row/s

LIMIT i OFFSET j
limit is amount of rows returnes and offset how many rows down it starts returning from

return null if no result

wrap my query with IFNULL function
SELECT(IFNULL((MYQUERY),NULL))

Not in VS not exists:

not in works when columns aren't nullable, if there is a column with a null value, it won't work.
not exists work for nullable colums

"If all you need is to check for matching rows in the other table but don’t need any columns from that table, use IN. If you do need columns from the second table, use Inner Join."

SELECT A.Name from Customers A
WHERE NOT EXISTS (SELECT 1 FROM Orders B WHERE A.Id = B.CustomerId)

something to note abt this query is that SQL doesn't send really send the resulting table of the subquery to the parent query.
the result of the subquery (from the example I ran) returns

so SQL either compares strictly by the keys or sends up (to parent query) the entire resulting rows (even if select changes the 'view').