SQL ” whot is sQL 2
SQL (Structured Queny Language) is aprogramming
language desiged for managing dotain relati onal
database.SqL has a Variety o 4anctionsthat o
allow ds
useng to read, manipulate, and chang e
data. Though sqL is
in seft ware devolopment, its also popula
commonly used byengineers uilh
data
“H% semantiollyeagytounderstandmdlet.
analysts for afeaw reason :
Becauge it Can be
amounts of data dineetly uhene tts slored,
used to access lange
aralysts don’t have to copy data into olhen
aplicat’ons.
Compared to speread shet tools, ata
analysis done in SQL is easy to audit ond
meanS no more Peplicate. or omalyetg,thismeans
looking for the aelleuithhe typo inthe Brmula.
” SELECT* Example.
Ihe lolouing sqL statement seleete all !he Column.s fromthealestable:
EXample- SELECT *FROM Sales;
“select columhs uise
Example-
SELECI year,
month west.
FROM Sales Rename Qolumns
Example SELECTcuest AS”westRegion” FRoM Sales
” LIMIT Clause
Ihe LIMIT elause is used to spect’y the number o records to petun
Example- SELECT* FROM Sales
LIMIT 100 WHERE Clause
Example- SELECT* FROM Sales
wHERE clause is used to ken
If is uged to extraet only those reconds lhat
The
fulf:ll a specifted eonattion.
reconds.
WHERE Country
” Canoda;
“
The most
Comppison Operatong on numepical dota
basie any toftlter data te uting Companison. operators. lhe easiect eway to
bndenstond them is lo stant by loking at a ltst of them:
Equal to
Not equalto Ghreater than
Less than
Greaten thon or equal to = L e s s t h a n o n e q u a l t t Oo
Exemple- . SELECT *
FROM Sales
WHERE City = kolkata”;
SELECT *
FROM Sales
WHERE city != “kolkata” ;
SELECT *
FROM Saleg
WHERE Month >”Jonuany”;
SELECT *
FROM Soles
WHERE Saleomount < 50000
Jou can. perlorm anithmetie in salusing the Some operators you. cuould in Exeel: + -)*, ouever
in saL you Can ony perform anithmetie acogg
Columons on values in agiven row. To clanify you Con only add values in multiple columns Arorn the
Same row
together asing
ad +-iyou want to od
Values
9Pegate Panetins.
Exemple-
acrogs multiple rocus, you’ll need to use
SELECTyeor months
west South,
wegt +southAs South.hsc FROM Sales ;
Example.
SELECT Jear,
months twest s
South,
west +south- 4year AS new.column
FROM Sales;
Example –
SELECT year, month
west Southy
(west +sout)/2AS Soulh uest-avg FROM Sales j
CREATE TABLE
The cREATE TABLE statement is uged to cneate anew toble in a.databage.
Example-
CREATE TABLE person.l PersonID ints
LastName Vanchar(255), FristNome varchar (255),
Aldness varchan (255) city varchor (255)
” INSERT INTO
The INSERT INTO SBatement is used to insert neau peconds in atable.
4. Speciky both the colum names and the values to be insented :
INSERTINTO table-name(Colunnd,colam2,eolumns,..) VALUES (value1, value2, Value3, …);
2. Ifyoane adding volueskr allthecolamsoPe tetl youdonotneedtoSpecttytheeoumnnamesin he
3aL query”
INSERT INTO table_hame VALUES(value1, \alue2,value3,…);
Whatis aNULL Value?
AReld aith a NULL Value is a keld uth n o
value.
IP afeld in atable is ogtional die possible to incept anew recond op update arecord
uilhoat adding avalue to this Reld. Then. lhe ield uill be “Saved uith a NULl value.
“Houto Testtor NULLValues?
possible to test for NylL walaes tuth
Companison operators, Sueh as =yk, op<>.
We udll have to use the IS NULL and IS NOT NUL
operatong inslead.
he ISNULLCperator
lhe TS NULL operator is used to test fop empty
yalues CNULL values).
Example-,
SELECT CustomerNome,corttactNome,Adtres
FROM Sales
WHERE Address IS NULL 3
The IS NoT NULL Operator
The TSNOTNDÊLoperatorisusedtotestfoo
non-empty yalues (NOT NULL Values).
Example –
SELECTcuskomenName, CatactName,Addresg
FROM Saleg
WHERE Address IS NoT NULL;
It isnot
” UPDATE Statement
The UPDATE stotement is used to madik he existing peconds inatobel.
Example
UPDATE Sales
SET ContactNane =” Alan”, city=” Goa” WAERE Customer ID= 1;
UPDATE Maltiple Records
Hig the WHERE clausge that detepmines howmany
pecopds enill be updated. Example
UPDATE Sales
SET PostalCode =00000 WHERE Cotry =India” ;
Notesi
Be Carelall eken. upateing recopds. T you omit
the WHEREclause,ALLPecondscuillbeupdated!
ment
Te DELETE statemernt is used to delete exisling pecords in a toble.
ExamplepEETE ROM Sales WHERE CustomerNarme=” Bob’:
DELETE State
Delete Al Recopdg
StnacBure,attribates,and inderes adl be itact:
Exmgle-
DELETE FROM toble_pame
“Altases
Aliases are used togve atabe, on a colun in
atable, atempoary n a m e .
Aliases are ol en ased to make coumn. Tames more peadable.
An alias ony exists Korthe duration olat queny. Analiagiscreatedtuththehs keyuOPd.
Alas Calumn Fxampe SELECTColumn_name AS alias_hame
FROM table name ;
” Alias Table Fxample. SELECT column.name (s)
I is
possible to delete all roue in atable uthout deleteing the table. This meang that the table
FROM table_name AS alias-name ;
“
Comporison openaton8 in One queny
Each logical openaton is ogpecialSnowtake, s0 uelgothroughthemindividualyin.the
follouinglessons.
“LIKE allow3, youtomateh simiar values, instead oF exaet vaues.
” I N a l l o w s , y o u t o s p e c k y a a kl ea t t o l u e s youwd like to include.
“BETWEEN allows you to select onl
wtthin a centain range.
” IS NULL allous you to seleet rous that
Contain. no data in a given eolumn. “AND allowe you to select only rous that
Batis?y tao condtlions.
“OR alloue you to select rout that satict either o tuo Conditions.
” NOT allous you to select powehat do not match a centain conditicn.
“LTKE Operator SELECT*
FROM Sales
SQL Logical
Logical operotons allowyou to use muiple
WHERE
gPoup” LIKE
‘New%;
Operators
“IN Operatop SELECT *
FROM Songs
WHERE artist IN(TaylorSuif.’Usher)3
“BETWEEN Operator SELECT*
FROM Songs
WHERE year_Pank BETWEEN 5 AND 10
” AND Operator SELECT *
FROM Songs
WHERE year =2012 AND yean Pank<=10;
OR OPeratop SELECT *
FROM Sorngs
WHEREyeaR_Pank =5oR artist =”Soru” ;
“NOT OPerator SELECT*
FROM Sales
WHERE NOT Country =”Jopan’
” Combining AND,OR and NOT SELECT FROM Saleg
WHERECounny-‘Tapan’ AND(CiBy-Go’oRcdy=’ruri)
” QRDER By SELECT
FROM Sales
ORDERBy Country, CustomerName j
SELECT* FROM Sales
ORDERBycoun:try Ase, CustomenNameDESC
Usigq CommentsCHowtouse Commente)
“SELECT — This is select command FROM Sales
WHERE year =2020;
“/” Here’s aComment So long and deseniptive that itcouldonlyft onmulliplelines.fortanately
it, too, will not aleet how thig code runs. */ SELECT*
FROM Sales WHERE year =2015 ;
SQL Aagnegate Funclton
functionsall thetime,s0ts impoptant toget Comfontable utth them.The functions themsaves
ane the Same ones you euill 4ind in Exeel or any other analyties rogram.
“COUNT Counts how many a row3 ane ina
Particulan column.
“SUMaslds togelhen all the values inaparlieular Colun.
“MIN and MAX peturn the louest and hi¡hest
Values inapartieular colanm, respetively:
“AVGCalculatestheaverageofagroup ofselected yalues.
sQL is
excellent
at aggregatingdatathe wayyou might in. a pivet table in Fxcel. You will ase oggrgate
Example
Examgle-
SELECT COUNT) FROM Saleg;
SELECTCOUNT (Column hame)
FROM Bable.name WHERE Codition;
S£LECT SUM (column
FROM toble name WAERE Condition ;
Example ‘-
name)
Example – SELECT MIN CColumn-name)
Example –
Example :-
FROM toble_name WHERE Condition;
S£LECT MAxCcolunnnome) FROM toble.name
WHERE Condition
SELECT Ava Ccolumn-hame)
FROM able.name WHERE Con.dition ;
The SQLGiROUPBYclause
GROUP 8S ollows youto Separate aata into groaps.,
chichcanbeaggregated independentlyofoneanodher.
SELECT year,
COUNT(*) AS Count
FROM Sales GAOUP BS year 3
Multile colamn
SELECT year, month,
COUNT() As Count FROM Saleg
GROUP 8y year,mondh;
GROUP BY
Column
Using
numbens SELECTyear,
morth, COUNTOAS count
FROM Sales GiROUP By 1,2
GhROUP BS wth ORDER BY
SELECT yeap, month,
COUNT)AS Count FROM &ales
GiROUP BN yean, month
ORDERBY
month,Jear i
Using GROUP BY audth IMTT SELECT Column hames
FROM table.name WHERE Condetien
GhROUP By column hame LIMIT umber ;
HAVING Clause
The HAVINGclause was added to SQL becuse
the WHERE keyuord cannot be used aith aggregate funetions.
Example :- SELECT Columnhame (3)
FROM able name
WHERE Condition
GROUP By column.name (s)
HAVING ondtion
ORDER By column-name () ;
” SELECT year month,
MAX(high)As monthhigh FROM Sales
GROUP AY yean, momih
HAVINGMAX(high) >4o0 ORDER BY year, month j
he SQl CASE statement
The aASE statement is sal’s way o handling il/ then logic. The CASE state ment is Rolowed by at
o
leastonepairo wAENandTHENstatements-sal’s
equivalent oP IF/THEN in Excel.Because o his pairing . you might be tempted to callthis sQL
CASE WHEN but CAsEisthe aceepBed term.
Every CAs£ sBatement mugt end with he END
statement. The ElSE satement ie optional,an
provides away to captune values not &pectfed itn the wHEN /TH3⁄4N Statement. cnsE is easiest to
understand in the conteyt ofan example.
Syntax
CASE
WHEN Cohdition1 THEN pesult 1
WHENCondition2 THENpesult2 WHEN ConditionNTHEN pesultN ELSE resul!
END ;
Example
CASE
SELECT OrderID, Quondty
WHENQuantity >30 THEN’hequontity is greato ba
WAENquantity=30THEN hequartityie 30″ ELSE “The quantity is under 20
END AS QuantityText FROM 8oes ;
SQL DISTINCT
Jou’ll occasionally want to look at nly the tunigue
particaar colunn. You Can do this using SELECT DISTINCT yntax.
values ina
Exomple:-
SELECT DISTINCTmonth FROM Sales ;
“SELECT DISTINCT year, month FROM Sales ;
Using
SELECT COUNT (DISTINCT month) AS Ungue-months
FROM Sales;
MySQL JOINS
A TOIN clause ts used to combine rows rom
two or more tables, based ona nelated column on
between them.
Fxampe: SELECT *
FROMbenn.calege-lotbal Playens playens JOIN benm. college.Poolballteams team3
ON teamg. 3ehool_namne =plasyers.sehol
Suppocted IypesoToTNSin MySQL Tges
“INNER JOIN:Retunns recordsthat have matching Values in. both tables.
“LEFT JOIN:Retuns all necopds homthelefl }oble. ond the matched recopds rom he night table.
“RIGHT JOIN : Returns all neconds rom the ight table,and the matched peopde from hele4 table.
” CROSS JoIN: Returns all records Brom bolh tables.
INNER
JOIN (ables
RIGHT JOIN
table2
LEFT table2 JOTN
CROSS 51Etno.e J0IN
DISTINCTin
agqreqations.
INNER J0IN
The INNER JOINkeyuond selects reconds lhat
have matchirg values in both tables. INNER JOIN
Example
The
(tabdlei tabe2)
SELECT column_hame(6) FROM table1
INNER JOINtable2
ON table ‘i. eolumn_name =table2.columnname;
LEFT J0TN
LEFTJoIN keyawordpeturnsallnenond3rom
(i! thelefltoble lable1),ondlhemalehingreconds(:f
ony) romthe ight table (lable2). LEFT JOIN
table2
Example
SELECT Column_name (s)
FROM table 1 LEFTJOIN table 2
ON tables.Columm.name toble2. Colmn hame;
RIGHT JOIN
Ihe RIGHTJOINkeyuondpetunnsallpecondsfrom
thenightiable(Aables),and!henalehig necrasti? any)romthe lelf table Cloble 1).
RIGHT JOIN
(tablei
Example:
SELECTColumn_name (s)
FROM Aable1
RTGHTJOIN table2
ON table1. Colunnname =toble 2.columnane ;
CROSS JOIN
The CROSsJOTN kejuordbetarnsallnecovdsrom both tales Ctable 1ond table 2).
CROSS JOIN
SELECT colum_name(9) FROM table 1
CROSS JOTIN toble 2;
SELF JOTN
Asel! Joinisareaularjoin,batthetableis Joined uith dsel.
Exomple :
UNTON Operator
sQLjoinsallowyo¡ tocornoine tu catageta Side-by. side, but UNTON allQwSyou to staek one dataset on top
oPthe other. Put differenlly, UNION allowsyou to
aurite two,SeparatesELECTStatements,andtohave the nesults o ene statemerd digplay in the Same table
SELECTaol umn-name (s) FROM table1 T1 ,table1.T2
WHERE Cendition
as fhe resultshrom the other
staBement.
Example i
CSELECTQolumn-name(s) FROMtable1
UNION SELECTCdeonn.name($)
” SELECT olumm_name (s) UNTONALL
SELECTcoumn_nane Cs)
IN Operator
FROM table 2; FROM Lable 1 FROM table 2;
The INopenaBorallousyoutoSpeciBymeltiple Values in a wHERE ause.
The TNopenaAor is a shorthond 4or mulliple oA Condetions.
Example :
SELECT FROMSales
WHERE Countny IN(“lndia’,”Nepal “UK);
SELECT* FROMSales
WHERECOuntny NOT TN(“India,”Nepal;”Ux):
SELECT FROMSales
WNERE Country TN (SELECTountry FROMSuqplien) ;
EXISTS Opera-or
The EXISTS openator is used to test for the evistence
ol any necord in a Subuery.
The EXISTS operatornelumgTRUE he Subqueny retuns ne on more records.
Exanple:
SELECT column name (8)
FBOM table-name WHERE EXISTS
(SELECTeoum.name FROMtahe.mame WHERECondetion);
ANYand ALL
The ANY and ALl eperator allou you to perform a
Comparison betueen Pange of olhen vaucs.
Operator
Single colamn value and
ANY OpemtoR
“Ipetuns abooleanvalueas aregult.
“It relupns TRUE P ANy o theSubquery valucs met the condition.
ANS means tat the condtion ul be t e ?he operotion is true lor any of the values nthe nonge.
Example:
SELECTProductName FROMSales
WHERE ProductID=ANY (SELECTProduetID FROMOrderDelails
WAEREQuantity >99)3
ALL Operator
” I reurns aboolean Vale as a pesult.
“IretuonsTRUE ?ALL?hesabgueryvaluestmeet the condition.
R is used with SElECT, WHEREad HAVINGstatements.
ALLmeansthattheconditionwillbetruecly ?Jhe Öperation is true fon all values in the ronge.
Example-
SELECT ALL Prodact Name FROM Sales
WHERE TRUE ;
SELECT ArodctNayme FROMSales WHERE ProductID = ALL
(SELECTProsucBID FROMOrderDetails WHERE Quantity =10)
INSERT NTO SELECT
Ihe INSERT INTO SELECT sBatemert copiesdata ro one table and inscrs tt into anadhentable.
The INSERT INTO SELEÇT staBement reguires thal the
dotatypes in source and
lhe existing Pecords in the tayget tabe are unalfected.
Exampe:-
” INSERT INTO table 2 SELECT FRDM toble 1
WHERE Condction ;
Bargel tables matches.
” INSERTINTOtble2Ccolanl,column2,Qolam3,.) SELECT Column 1,Colunn2, Colurn3,…
FROM toble1 WHERE cendition
INSERT INTOSBatement
The INSERTINIO statement is used o insert rew recopds ën a toble.
IH is posible to write the INSERTINTOstaement in two uoys.
“Specrtybl, tre colunrames and the valaes lobe inserted.
INSERT INTOtabe. nane (oskomnlsColunn2, aumng,..) VALUES (VAluei, Value2, value3, …)3
” I I youape odding values forall he cdlums oPhe table
youdonotneedtosperikythecolurmvamgs inthesaL
quey. However, make Sure the orden o the values js in the Same orden as the columns inhe table. tlene, the
INSERT INTO Syntax would be as folous.
INSERT INTO tabe.name VALUES(Valuei,value2, Value3,…);
IFNULLOFuncBton.
IFNULLO.4nction lets yourelurn onalternatire vaue !
an expression is NULL.
TIhe example below relurns 0 ? h e value is NULL.
SELECTCartactnane, IFNULL(bizphone,homephone)As phone
FROM contacts
” SELECT mame
IFNULLCORtcephone ,mbilephone)AScontact
FROMemployee j
SQL Basics Cheat Sheet
SQL
SQL, or Structured Query Language, is a language to talk to databases. It allows you to select specific data and to build complex reports. Today, SQL is a universal language of data. It is used in practically all technologies that process data.
SAMPLE DATA
| COUNTRY | ||||
| id | name | population | area | |
| 1 | France | 66600000 | 640680 | |
| 2 | Germany | 80700000 | 357000 | |
| … | … | … | … | |
| CITY | ||||
| id | name | country_id population | rating | |
| 1 | Paris | 1 | 2243000 | 5 |
| 2 | Berlin | 2 | 3460000 | 3 |
| … | … | … | … | … |
FILTERING THE OUTPUT
COMPARISON OPERATORS
Fetch names of cities that have a rating above 3:
SELECT name
FROM city
WHERE rating > 3;
Fetch names of cities that are neither Berlin nor Madrid:
SELECT name
FROM city
WHERE name != ‘Berlin’
AND name != ‘Madrid’;
TEXT OPERATORS
QUERYING MULTIPLE TABLES
INNER JOIN
JOIN (or explicitly INNER JOIN) returns rows that have matching values in both tables.
SELECT city.name, country.name FROM city
[INNER] JOIN country
ON city.country_id = country.id;
| CITY | COUNTRY | |||
| id | name | country_id | id | name |
| 1 | Paris | 1 | 1 | France |
| 2 | Berlin | 2 | 2 | Germany |
| 3 | Warsaw | 4 | 3 | Iceland |
FULL JOIN
FULL JOIN (or explicitly FULL OUTER JOIN ) returns all rows from both tables – if there’s no matching row in the second table, NULLs are returned.
SELECT city.name, country.name FROM city
FULL [OUTER] JOIN country
ON city.country_id = country.id;
| CITY | COUNTRY | |||
| id | name | country_id | id | name |
| 1 | Paris | 1 | 1 | France |
| 2 | Berlin | 2 | 2 | Germany |
| 3 | Warsaw | 4 | NULL | NULL |
| NULL | NULL | NULL | 3 | Iceland |
QUERYING SINGLE TABLE
Fetch all columns from the country table:
SELECT *
FROM country;
Fetch id and name columns from the city table:
SELECT id, name
FROM city;
Fetch city names sorted by the rating column in the default ASCending order:
SELECT name
FROM city
ORDER BY rating [ASC];
Fetch city names sorted by the rating column in the DESCending order:
Fetch names of cities that start with a ‘P’ or end with an ‘s’:
SELECT name
FROM city
WHERE name LIKE ‘P%’
OR name LIKE ‘%s’;
Fetch names of cities that start with any letter followed by ‘ublin’ (like Dublin in Ireland or Lublin in Poland):
SELECT name
FROM city
WHERE name LIKE ‘_ublin’;
OTHER OPERATORS
Fetch names of cities that have a population between 500K and 5M:
LEFT JOIN
LEFT JOIN returns all rows from the left table with corresponding rows from the right table. If there’s no matching row, NULLs are returned as values from the second table.
SELECT city.name, country.name FROM city
LEFT JOIN country
ON city.country_id = country.id;
| CITY | COUNTRY | |||
| id | name | country_id | id | name |
| 1 | Paris | 1 | 1 | France |
| 2 | Berlin | 2 | 2 | Germany |
| 3 | Warsaw | 4 | NULL | NULL |
CROSS JOIN
CROSS JOIN returns all possible combinations of rows from both tables. There are two syntaxes available.
SELECT city.name, country.name FROM city
CROSS JOIN country;
SELECT city.name, country.name
FROM city, country;
| CITY | COUNTRY | |||
| id | name | country_id | id | name |
| 1 | Paris | 1 | 1 | France |
| 1 | Paris | 1 | 2 | Germany |
| 2 | Berlin | 2 | 1 | France |
| 2 | Berlin | 2 | 2 | Germany |
SELECT name
FROM city
ORDER BY rating DESC;
ALIASES
COLUMNS
SELECT name AS city_name FROM city;
TABLES
SELECT co.name, ci.name
FROM city AS ci
JOIN country AS co
ON ci.country_id = co.id;
SELECT name
FROM city
WHERE population BETWEEN 500000 AND 5000000;
Fetch names of cities that don’t miss a rating value:
SELECT name
FROM city
WHERE rating IS NOT NULL;
Fetch names of cities that are in countries with IDs 1, 4, 7, or 8:
SELECT name
FROM city
WHERE country_id IN (1, 4, 7, 8);
RIGHT JOIN
RIGHT JOIN returns all rows from the right table with corresponding rows from the left table. If there’s no matching row, NULLs are returned as values from the left table.
SELECT city.name, country.name FROM city
RIGHT JOIN country
ON city.country_id = country.id;
| CITY | COUNTRY | |||
| id | name | country_id | id | name |
| 1 | Paris | 1 | 1 | France |
| 2 | Berlin | 2 | 2 | Germany |
| NULL | NULL | NULL | 3 | Iceland |
NATURAL JOIN
NATURAL JOIN will join tables by all columns with the same name.
SELECT city.name, country.name FROM city
NATURAL JOIN country;
| CITY | COUNTRY | |||
| country_id | id | name | name | id |
| 6 | 6 | San Marino | San Marino | 6 |
| 7 | 7 | Vatican City Vatican City | 7 | |
| 5 | 9 | Greece | Greece | 9 |
| 10 | 11 | Monaco | Monaco | 10 |
NATURAL JOIN used these columns to match rows: city.id, city.name, country.id, country.name NATURAL JOIN is very rarely used in practice.
Try out the interactive SQL Basics course at LearnSQL.com, and check out our other SQL courses.
LearnSQL.com is owned by Vertabelo SA vertabelo.com | CC BY-NC-ND Vertabelo SA
SQL Basics Cheat Sheet
AGGREGATION AND GROUPING
GROUP BY groups together rows that have the same values in specified columns.
It computes summaries (aggregates) for each unique combination of values.
SUBQUERIES
A subquery is a query that is nested inside another query, or inside another subquery.
There are different types of subqueries.
SET OPERATIONS
Set operations are used to combine the results of two or more queries into a single result. The combined queries must return the same number of columns and compatible data types. The names of the corresponding columns can be different.
| CITY | ||
| id | name | country_id |
| 1 | Paris | 1 |
| 101 | Marseille | 1 |
| 102 | Lyon | 1 |
| 2 | Berlin | 2 |
| 103 | Hamburg | 2 |
| 104 | Munich | 2 |
| 3 | Warsaw | 4 |
| 105 | Cracow | 4 |
CITY
country_id count
- 3
- 3
- 2
SINGLE VALUE
The simplest subquery returns exactly one column and exactly one row. It can be used with comparison operators =, <, <=, >, or >=.
This query finds cities with the same rating as Paris:
SELECT name FROM city
WHERE rating = (
SELECT rating
FROM city
WHERE name = ‘Paris’
| CYCLING | SKATING | ||||
| id | name | country | id | name | country |
| 1 | YK | DE | 1 | YK | DE |
| 2 | ZG | DE | 2 | DF | DE |
| 3 | WT | PL | 3 | AK | PL |
| … | … | … | … | … | … |
UNION
AGGREGATE FUNCTIONS
- avg(expr) − average value for rows within the group
- count(expr) − count of values for rows within the group • max(expr) − maximum value within the group
- min(expr) − minimum value within the group • sum(expr) − sum of values within the group
EXAMPLE QUERIES
Find out the number of cities:
SELECT COUNT(*)
FROM city;
Find out the number of cities with non-null ratings:
SELECT COUNT(rating)
FROM city;
Find out the number of distinctive country values:
SELECT COUNT(DISTINCT country_id)
FROM city;
Find out the smallest and the greatest country populations:
SELECT MIN(population), MAX(population)
FROM country;
Find out the total population of cities in respective countries:
SELECT country_id, SUM(population)
FROM city
GROUP BY country_id;
Find out the average rating for cities in respective countries if the average is above 3.0:
SELECT country_id, AVG(rating)
FROM city
GROUP BY country_id
HAVING AVG(rating) > 3.0;
);
MULTIPLE VALUES
A subquery can also return multiple columns or multiple rows. Such subqueries can be used with operators IN, EXISTS, ALL, or ANY.
This query finds cities in countries that have a population above 20M:
SELECT name
FROM city
WHERE country_id IN (
SELECT country_id
FROM country
WHERE population > 20000000
);
CORRELATED
A correlated subquery refers to the tables introduced in the outer query. A correlated subquery depends on the outer query. It cannot be run independently from the outer query.
This query finds cities with a population greater than the average population in the country:
SELECT *
FROM city main_city
WHERE population > (
SELECT AVG(population)
FROM city average_city
WHERE average_city.country_id = main_city.country_id
);
This query finds countries that have at least one city:
SELECT name
FROM country
WHERE EXISTS (
SELECT *
FROM city
WHERE country_id = country.id
);
UNION combines the results of two result sets and removes duplicates.
UNION ALL doesn’t remove duplicate rows.
This query displays German cyclists together with German skaters:
SELECT name
FROM cycling
WHERE country = ‘DE’
UNION / UNION ALL
SELECT name
FROM skating
WHERE country = ‘DE’;
INTERSECT
INTERSECT returns only rows that appear in both result sets.
This query displays German cyclists who are also German skaters at the same time:
SELECT name
FROM cycling
WHERE country = ‘DE’
INTERSECT
SELECT name
FROM skating
WHERE country = ‘DE’;
EXCEPT
EXCEPT returns only the rows that appear in the first result set but do not appear in the second result set.
This query displays German cyclists unless they are also German skaters at the same time:
SELECT name
FROM cycling
WHERE country = ‘DE’
EXCEPT / MINUS
SELECT name
FROM skating
WHERE country = ‘DE’;
Try out the interactive SQL Basics course at LearnSQL.com, and check out our other SQL courses.
LearnSQL.com is owned by Vertabelo SA vertabelo.com | CC BY-NC-ND Vertabelo SA
SQL Window Functions Cheat Sheet
WINDOW FUNCTIONS
compute their result based on a sliding window frame, a set of rows that are somehow related to
AGGREGATE FUNCTIONS VS. WINDOW FUNCTIONS
unlike aggregate functions, window functions do not collapse rows.
PARTITION BY
divides rows into multiple groups, called partitions, to which the window function is applied.
ORDER BY
specifies the order of rows in each partition to which the window function is applied.
the current row.
PARTITION BY city
PARTITION BY city ORDER BY month
current row
Aggregate Functions
∑
Window Functions
∑
∑
∑
| month | city | sold |
| 1 | Rome | 200 |
| 2 | Paris | 500 |
| 1 | London | 100 |
| 1 | Paris | 300 |
| 2 | Rome | 300 |
- London 400
- Rome400
| month | city | sold | sum | ||
| 1 | Paris | 300 | 800 | ||
| 2 | Paris | 500 | 800 | ||
| 1 | Rome | 200 | 900 | ||
| 2 | Rome | 300 | 900 | ||
| 3 | Rome | 400 | 900 | ||
| 1 | London | 500 | |||
| 100 | |||||
| 2 | London | 400 | 500 |
| sold | city | month |
| 200 | Rome | 1 |
| 500 | Paris | 2 |
| 100 | London | 1 |
| 300 | Paris | 1 |
| 300 | Rome | 2 |
| 400 | London | 2 |
| 400 | Rome | 3 |
| sold | city | month |
| 300 | Paris | 1 |
| 500 | Paris | 2 |
| 200 | Rome | 1 |
| 300 | Rome | 2 |
| 400 | Rome | 3 |
| 100 | London | 1 |
| 400 | London | 2 |
SYNTAX
Default Partition: with no PARTITION BY clause, the entire
result set is the partition.
Default ORDER BY: with no ORDER BY clause, the order of rows within each partition is arbitrary.
SELECT city, month, SELECT <column_1>, <column_2>,
sum(sold) OVER ( <window_function>() OVER (
PARTITION BY city PARTITION BY <…>
ORDER BY month ORDER BY <…>
RANGE UNBOUNDED PRECEDING) total <window_frame>) <window_column_alias>
WINDOW FRAME
is a set of rows that are somehow related to the current row. The window frame is evaluated separately within each partition.
ROWS | RANGE | GROUPS BETWEEN lower_bound AND upper_bound
FROM sales; FROM <table_name>;
N PRECEDING
PARTITION
UNBOUNDED
PRECEDING
The bounds can be any of the five options:
Named Window Definition
SELECT country, city,
rank() OVER country_sold_avg FROM sales
WHERE month BETWEEN 1 AND 6 GROUP BY country, city
SELECT <column_1>, <column_2>,
<window_function>() OVER <window_name>
FROM <table_name>
WHERE <…>
GROUP BY <…>
CURRENT
ROW
M FOLLOWING
- ROWS M ROWS
UNBOUNDED
FOLLOWING
∙ UNBOUNDED PRECEDING
∙ n PRECEDING
∙ CURRENT ROW
∙ n FOLLOWING
∙ UNBOUNDED FOLLOWING
The lower_bound must be BEFORE the upper_bound
HAVING sum(sold) > 10000 WINDOW country_sold_avg AS (
PARTITION BY country ORDER BY avg(sold) DESC)
ORDER BY country, city;
HAVING <…>
WINDOW <window_name> AS (
PARTITION BY <…>
ORDER BY <…>
<window_frame>)
ORDER BY <…>;
| ROWS BETWEEN 1 PRECEDING | RANGE BETWEEN 1 PRECEDING | GROUPS BETWEEN 1 PRECEDING | |||||||||||||||||||||||
| AND 1 FOLLOWING | AND 1 FOLLOWING | AND 1 FOLLOWING | |||||||||||||||||||||||
| city | sold | month | city | sold | month | city | sold | month | |||||||||||||||||
| Paris | 300 | 1 | Paris | 300 | 1 | Paris | 300 | 1 | |||||||||||||||||
| Rome | 200 | 1 | Rome | 200 | 1 | Rome | 200 | 1 | |||||||||||||||||
| Paris | 500 | 2 | Paris | 500 | 2 | Paris | 500 | 2 | |||||||||||||||||
| current | Rome | 100 | 4 | current | Rome | 100 | 4 | current | Rome | 100 | 4 | ||||||||||||||
| row | Paris | 200 | 4 | row | Paris | 200 | 4 | row | Paris | 200 | 4 | ||||||||||||||
| Paris | 300 | 5 | Paris | 300 | 5 | Paris | 300 | 5 | |||||||||||||||||
PARTITION BY, ORDER BY, and window frame definition are all optional.
LOGICAL ORDER OF OPERATIONS IN SQL
| 1. | FROM, JOIN | 7. | SELECT |
| 2. | WHERE | 8. | DISTINCT |
| 3. | GROUP BY | 9. | UNION/INTERSECT/EXCEPT |
| Rome | 200 | 5 | Rome | 200 | 5 | Rome | 200 | 5 | |||||||
| London | 200 | 5 | London | 200 | 5 | London | 200 | 5 | |||||||
| London | 100 | 6 | London | 100 | 6 | London | 100 | 6 | |||||||
| Rome | 300 | 6 | Rome | 300 | 6 | Rome | 300 | 6 | |||||||
| 1 row before the current row and | values in the range between 3 and 5 | 1 group before the current row and 1 group | |||||||||||||
| 1 row after the current row | ORDER BY must contain a single expression | after the current row regardless of the value | |||||||||||||
As of 2020, GROUPS is only supported in PostgreSQL 11 and up.
| 4. | aggregate functions | 10. | ORDER BY |
| 5. | HAVING | 11. | OFFSET |
| 6. | window functions | 12. | LIMIT/FETCH/TOP |
You can use window functions in SELECT and ORDER BY. However, you can’t put window functions anywhere in the FROM, WHERE, GROUP BY, or HAVING clauses.
ABBREVIATIONS
| Abbreviation | Meaning |
| UNBOUNDED PRECEDING | BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
| n PRECEDING | BETWEEN n PRECEDING AND CURRENT ROW |
| CURRENT ROW | BETWEEN CURRENT ROW AND CURRENT ROW |
| n FOLLOWING | BETWEEN AND CURRENT ROW AND n FOLLOWING |
| UNBOUNDED FOLLOWING | BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
DEFAULT WINDOW FRAME
If ORDER BY is specified, then the frame is
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Without ORDER BY, the frame specification is
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
Try out the interactive Window Functions course at LearnSQL.com, and check out our other SQL courses.
LearnSQL.com is owned by Vertabelo SA vertabelo.com | CC BY-NC-ND Vertabelo SA
SQL Window Functions Cheat Sheet
LIST OF WINDOW FUNCTIONS
Aggregate Functions
∙ avg()
∙ count()
∙ max()
RANKING FUNCTIONS
∙row_number() − unique number for each row within partition, with different numbers for tied values
∙rank() − ranking within partition, with gaps and same ranking for tied values
∙dense_rank() − ranking within partition, with no gaps and same ranking for tied values
DISTRIBUTION FUNCTIONS
∙percent_rank() − the percentile ranking number of a row—a value in [0, 1] interval:
(rank – 1) / (total number of rows – 1)
∙cume_dist() − the cumulative distribution of a value within a group of values, i.e., the number of rows with values less than or equal to the current row’s value divided by the total number of rows; a value in (0, 1] interval
∙min()
∙ sum()
Ranking Functions
∙row_number()
∙rank()
∙dense_rank()
| city | price | row_number | rank | dense_rank | |
| over(order by price) | |||||
| Paris | 7 | 1 | 1 | 1 | |
| Rome | 7 | 2 | 1 | 1 | |
| London | 8.5 | 3 | 3 | 2 | |
| Berlin | 8.5 | 4 | 3 | 2 | |
| Moscow | 9 | 5 | 5 | 3 | |
| Madrid | 10 | 6 | 6 | 4 | |
| Oslo | 10 | 7 | 6 | 4 |
percent_rank() OVER(ORDER BY sold)
| city | sold | percent_rank | |||
| Paris | 100 | 0 | |||
| Berlin | 150 | 0.25 | |||
| Rome | 200 | 0.5 | without this row 50% of | ||
| Moscow | 200 | 0.5 | |||
| values are less than this | |||||
| London | 300 | 1 | |||
| row’s value | |||||
cume_dist() OVER(ORDER BY sold)
| city | sold | cume_dist | |||
| Paris | 100 | 0.2 | |||
| Berlin | 150 | 0.4 | |||
| Rome | 200 | 0.8 | 80% of values are | ||
| Moscow | 200 | 0.8 | |||
| less than or equal | |||||
| London | 300 | 1 | |||
| to this one | |||||
Distribution Functions
∙ percent_rank()
∙ cume_dist()
Analytic Functions
∙ lead()
∙ lag()
∙ntile()
ORDER BY and Window Frame: rank() and dense_rank() require ORDER BY, but row_number() does not require ORDER BY. Ranking functions do not accept window frame definition (ROWS, RANGE, GROUPS).
ANALYTIC FUNCTIONS
∙lead(expr, offset, default) − the value for the row offset rows after the current; offset and default are optional; default values: offset = 1, default = NULL
ORDER BY and Window Frame: Distribution functions require ORDER BY. They do not accept window frame definition (ROWS, RANGE, GROUPS).
∙ first_value(expr) − the value for the first row within the window frame ∙last_value(expr) − the value for the last row within the window frame
∙ first_value()
∙ last_value()
∙ nth_value()
∙lag(expr, offset, default) − the value for the row offset rows before the current; offset and default are optional; default values: offset = 1, default = NULL
lead(sold) OVER(ORDER BY month) lag(sold) OVER(ORDER BY month)
first_value(sold) OVER
(PARTITION BY city ORDER BY month)
city month sold first_value
Paris 1 500 500
last _value(sold) OVER (PARTITION BY city ORDER BY month RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AGGREGATE FUNCTIONS
∙avg(expr) − average value for rows within the window frame
| month | month | sold | ||
| 1 | 500 | 300 | ||
| 2 | 300 | 400 | ||
| by | ||||
| 3 | 400 | 100 | ||
| order | ||||
| 4 | 100 | 500 | ||
| 5 | 500 | NULL |
lead(sold, 2, 0) OVER(ORDER BY month)
| month | month | sold | ||
| 1 | 500 | NULL | ||
| 2 | 300 | 500 | ||
| by | ||||
| 3 | 400 | 300 | ||
| order | ||||
| 4 | 100 | 400 | ||
| 5 | 500 | 100 |
lag(sold, 2, 0) OVER(ORDER BY month)
| Paris | 2 | 300 | 500 |
| Paris | 3 | 400 | 500 |
| Rome | 2 | 200 | 200 |
| Rome | 3 | 300 | 200 |
| Rome | 4 | 500 | 200 |
| city | month | sold | last_value |
| Paris | 1 | 500 | 400 |
| Paris | 2 | 300 | 400 |
| Paris | 3 | 400 | 400 |
| Rome | 2 | 200 | 500 |
| Rome | 3 | 300 | 500 |
| Rome | 4 | 500 | 500 |
∙count(expr) − count of values for rows within the window frame
∙ max(expr) − maximum value within the window frame
∙min(expr) − minimum value within the window frame
| bymonth | month | sold | bymonth | month | sold | offset=2 | ||||
| 1 | 500 | 400 | 1 | 500 | 0 | |||||
| 2 | 300 | 100 | 2 | 300 | 0 | |||||
| order | 3 | 400 | 500 | offset=2 | order | 3 | 400 | 500 | ||
| 4 | 100 | 0 | 4 | 100 | 300 | |||||
| 5 | 500 | 0 | 5 | 500 | 400 |
∙ntile(n) − divide rows within a partition as equally as possible into n groups, and assign each row its group number.
Note: You usually want to use RANGE BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING with last_value(). With the default
window frame for ORDER BY, RANGE UNBOUNDED
PRECEDING, last_value() returns the value for
the current row.
∙nth_value(expr, n) − the value for the n-th row within the window frame; n must be an integer
nth_value(sold, 2) OVER (PARTITION BY city
ORDER BY month RANGE BETWEEN UNBOUNDED
ntile(3)
PRECEDING AND UNBOUNDED FOLLOWING)
∙sum(expr) − sum of values within the window frame
ORDER BY and Window Frame: Aggregate functions do not require an ORDER BY. They accept window frame definition (ROWS, RANGE, GROUPS).
| city | sold |
| Rome | 100 |
| Paris | 100 |
| London | 200 |
| Moscow | 200 |
| Berlin | 200 |
| Madrid | 300 |
| Oslo | 300 |
| Dublin | 300 |
1
2
3
1
1
1
2
2
2
3
3
ORDER BY and Window Frame: ntile(), lead(), and lag() require an ORDER BY. They do not accept window frame definition (ROWS, RANGE, GROUPS).
| city | month | sold |
| Paris | 1 | 500 |
| Paris | 2 | 300 |
| Paris | 3 | 400 |
| Rome | 2 | 200 |
| Rome | 3 | 300 |
| Rome | 4 | 500 |
| Rome | 5 | 300 |
| London | 1 | 100 |
nth_value
300
300
300
300
300
300
300
NULL
ORDER BY and Window Frame: first_value(), last_value(), and nth_value() do not require an ORDER BY. They accept window frame definition (ROWS, RANGE, GROUPS).
Try out the interactive Window Functions course at LearnSQL.com, and check out our other SQL courses.
LearnSQL.com is owned by Vertabelo SA vertabelo.com | CC BY-NC-ND Vertabelo SA
SQL JOINs Cheat Sheet
JOINING TABLES
JOIN combines data from two tables.
| TOY | toy_name | cat_id | CAT | ||
| toy_id | cat_id cat_name | ||||
| 1 | ball | 3 | 1 | Kitty | |
| 2 | spring | NULL | 2 | Hugo | |
| 3 | mouse | 1 | 3 | Sam | |
| 4 | mouse | 4 | 4 | Misty | |
| 5 | ball | 1 | |||
JOIN typically combines rows with equal values for the specified columns. Usually, one table contains a primary key, which is a column or columns that uniquely identify rows in the table (the cat_id column in the cat table).
The other table has a column or columns that refer to the primary key columns in the first table (the cat_id column in the toy table). Such columns are foreign keys . The JOIN condition is the equality between the primary key columns in one table and columns referring to them in the other table.
JOIN
JOIN returns all rows that match the ON condition. JOIN is also called INNER JOIN.
| SELECT * | toy_id | toy_name cat_id | cat_id cat_name | |||
| FROM | toy | 5 | ball | 1 | 1 | Kitty |
| JOIN cat | 3 | mouse | 1 | 1 | Kitty | |
| ON | toy.cat_id = cat.cat_id; | 1 | ball | 3 | 3 | Sam |
| 4 | mouse | 4 | 4 | Misty | ||
There is also another, older syntax, but it isn’t recommended.
List joined tables in the FROM clause, and place the conditions in the WHERE clause.
SELECT *
FROM toy, cat
LEFT JOIN
LEFT JOIN returns all rows from the left table with matching rows from the right table. Rows without a match are filled with NULLs. LEFT JOIN is also called LEFT OUTER JOIN.
| SELECT * | toy_id | toy_name cat_id | cat_id cat_name | ||
| FROM toy | 5 | ball | 1 | 1 | Kitty |
| LEFT JOIN cat | 3 | mouse | 1 | 1 | Kitty |
| ON toy.cat_id = cat.cat_id; | 1 | ball | 3 | 3 | Sam |
| 4 | mouse | 4 | 4 | Misty | |
| 2 | spring | NULL | NULL | NULL | |
| whole left table | |||||
RIGHT JOIN
RIGHT JOIN returns all rows from the right table with matching rows from the left table. Rows without a match are filled with NULLs. RIGHT JOIN is also called RIGHT OUTER JOIN.
| SELECT * | toy_id | toy_name cat_id | cat_id cat_name | ||
| FROM toy | 5 | ball | 1 | 1 | Kitty |
| RIGHT JOIN cat | 3 | mouse | 1 | 1 | Kitty |
| ON toy.cat_id = cat.cat_id; | NULL | NULL | NULL | 2 | Hugo |
| 1 | ball | 3 | 3 | Sam | |
| 4 | mouse | 4 | 4 | Misty | |
| whole right table | |||||
FULL JOIN
FULL JOIN returns all rows from the left table and all rows from the right table. It fills the non-matching rows with NULLs. FULL JOIN is also called FULL OUTER JOIN.
WHERE toy.cat_id = cat.cat_id;
JOIN CONDITIONS
The JOIN condition doesn’t have to be an equality – it can be any condition you want. JOIN doesn’t interpret the JOIN condition, it only checks if the rows satisfy the given condition.
To refer to a column in the JOIN query, you have to use the full column name: first the table name, then a dot (.) and the column name:
SELECT *
FROM toy
FULL JOIN cat
ON toy.cat_id = cat.cat_id;
| toy_id | toy_name cat_id | cat_id cat_name | ||
| 5 | ball | 1 | 1 | Kitty |
| 3 | mouse | 1 | 1 | Kitty |
| NULL | NULL | NULL | 2 | Hugo |
| 1 | ball | 3 | 3 | Sam |
| 4 | mouse | 4 | 4 | Misty |
| 2 | spring | NULL | NULL | NULL |
| whole left table | whole right table | |||
ON cat.cat_id = toy.cat_id
You can omit the table name and use just the column name if the name of the column is unique within all columns in the joined tables.
CROSS JOIN
CROSS JOIN returns all possible combinations of rows from the left and right tables.
NATURAL JOIN
If the tables have columns with the same name, you can use NATURAL JOIN instead of JOIN.
SELECT *
FROM toy
NATURAL JOIN cat;
The common column appears only once in the result table.
Note: NATURAL JOIN is rarely used in real life.
| cat_id | toy_id | toy_name cat_name | |
| 1 | 5 | ball | Kitty |
| 1 | 3 | mouse | Kitty |
| 3 | 1 | ball | Sam |
| 4 | 4 | mouse | Misty |
SELECT *
FROM toy
CROSS JOIN cat;
Other syntax:
SELECT *
FROM toy, cat;
| toy_id | toy_name cat_id | cat_id cat_name | ||
| 1 | ball | 3 | 1 | Kitty |
| 2 | spring | NULL | 1 | Kitty |
| 3 | mouse | 1 | 1 | Kitty |
| 4 | mouse | 4 | 1 | Kitty |
| 5 | ball | 1 | 1 | Kitty |
| 1 | ball | 3 | 2 | Hugo |
| 2 | spring | NULL | 2 | Hugo |
| 3 | mouse | 1 | 2 | Hugo |
| 4 | mouse | 4 | 2 | Hugo |
| 5 | ball | 1 | 2 | Hugo |
| 1 | ball | 3 | 3 | Sam |
| ··· | ··· | ··· | ··· | ··· |
Try out the interactive SQL JOINs course at LearnSQL.com, and check out our other SQL courses.
LearnSQL.com is owned by Vertabelo SA vertabelo.com | CC BY-NC-ND Vertabelo SA
SQL JOINs Cheat Sheet
COLUMN AND TABLE ALIASES
Aliases give a temporary name to a table or a column in a table.
| CAT AS c | mom_id | owner_id | ||
| cat_id cat_name | ||||
| 1 | Kitty | 5 | 1 | |
| 2 | Hugo | 1 | 2 | |
| 3 | Sam | 2 | 2 | |
| 4 | Misty | 1 | NULL | |
OWNER AS o
id name
- John Smith
- Danielle Davis
MULTIPLE JOINS
You can join more than two tables together. First, two tables are joined, then the third table is joined to the result of the previous joining.
| TOY AS t | CAT AS c | OWNER AS o | |||||||
| toy_id | toy_name cat_id | ||||||||
| cat_id | cat_name | mom_id | owner_id | ||||||
| 1 | ball | 3 | id | name | |||||
| 1 | Kitty | 5 | 1 | ||||||
| 2 | spring | NULL | 1 | John | |||||
| 2 | Hugo | 1 | 2 | Smith | |||||
A column alias renames a column in the result. A table alias renames a table within the query. If you define a table alias, you must use it instead of the table name everywhere in the query. The AS keyword is optional in defining aliases.
| SELECT | ||
| o.name AS owner_name, | cat_name | owner_name |
| 3 | mouse | 1 | 3 | Sam | 2 | 2 | 2 | Danielle | |
| 4 | mouse | 4 | Davis | ||||||
| 5 | ball | 1 | 4 | Misty | 1 | NULL | |||
| c.cat_name | Kitty | John Smith |
| FROM cat AS c | Sam | Danielle Davis |
| JOIN owner AS o | Hugo | Danielle Davis |
| ON c.owner_id = o.id; |
SELF JOIN
You can join a table to itself, for example, to show a parent-child relationship.
| CAT AS child | mom_id | CAT AS mom | mom_id | |||||
| cat_id | cat_name owner_id | cat_id | cat_name owner_id | |||||
| 1 | Kitty | 1 | 5 | 1 | Kitty | 1 | 5 | |
| 2 | Hugo | 2 | 1 | 2 | Hugo | 2 | 1 | |
| 3 | Sam | 2 | 2 | 3 | Sam | 2 | 2 | |
| 4 | Misty | NULL | 1 | 4 | Misty | NULL | 1 | |
Each occurrence of the table must be given a different alias. Each column reference must be preceded with an
JOIN & JOIN
SELECT
t.toy_name,
c.cat_name,
o.name AS owner_name FROM toy t
JOIN cat c
ON t.cat_id = c.cat_id JOIN owner o
ON c.owner_id = o.id;
| toy_name | cat_name | owner_name | |
| ball | Kitty | John | Smith |
| mouse | Kitty | John | Smith |
| ball | Sam | Danielle Davis | |
JOIN & LEFT JOIN
SELECT
t.toy_name,
c.cat_name,
o.name AS owner_name FROM toy t
JOIN cat c
ON t.cat _id = c.cat_id LEFT JOIN owner o
ON c.owner_id = o.id;
| toy_name | cat_name | owner_name | |
| ball | Kitty | John | Smith |
| mouse | Kitty | John | Smith |
| ball | Sam | Danielle Davis | |
| mouse | Misty | NULL | |
LEFT JOIN & LEFT JOIN
SELECT
t.toy_name,
c.cat_name,
o.name AS owner_name FROM toy t
LEFT JOIN cat c
ON t.cat _id = c.cat_id LEFT JOIN owner o
ON c.owner_id = o.id;
| toy_name | cat_name | owner_name |
| ball | Kitty | John Smith |
| mouse | Kitty | John Smith |
| ball | Sam | Danielle Davis |
| mouse | Misty | NULL |
| spring | NULL | NULL |
appropriate table alias.
SELECT
child.cat_name AS child_name, mom.cat_name AS mom_name
FROM cat AS child
JOIN cat AS mom
ON child.mom_id = mom.cat_id;
| child_name | mom_name |
| Hugo | Kitty |
| Sam | Hugo |
| Misty | Kitty |
JOIN WITH MULTIPLE CONDITIONS
You can use multiple JOIN conditions using the ON keyword once and the AND keywords as many times as you need.
NON-EQUI SELF JOIN
You can use a non-equality in the ON condition, for example, to show all different pairs of rows.
| CAT AS c | mom_id | owner_id | age | OWNER AS o | age | ||||
| cat_id cat_name | id | name | |||||||
| 1 | Kitty | 5 | 1 | 17 | 1 | John Smith | 18 | ||
| 2 | Hugo | 1 | 2 | 10 | 2 | Danielle Davis | 10 | ||
| TOY AS a | toy_name cat_id | ||
| toy_id | |||
| 3 | mouse | ||
| 1 | |||
| 5 | ball | 1 | |
| 1 | ball | 3 | |
| 4 | mouse | 4 | |
| 2 | spring | NULL | |
SELECT
a.toy_name AS toy_a,
b.toy_name AS toy_b
FROM toy a
JOIN toy b
ON a.cat_id < b.cat_id;
| TOY AS b | toy_id toy_name | ||
| cat_id | |||
| 1 | 3 | mouse | |
| 1 | 5 | ball | |
| 3 | 1 | ball | |
| 4 | 4 | mouse | |
| NULL | 2 | spring | |
| cat_a_id | toy_a | cat_b_id | toy_b |
| 1 | mouse | 3 | ball |
| 1 | ball | 3 | ball |
| 1 | mouse | 4 | mouse |
| 1 | ball | 4 | mouse |
| 3 | ball | 4 | mouse |
| 3 | Sam | 2 | 2 | 5 | |||
| 4 | Misty | 1 | NULL | 11 | |||
| SELECT | |||||||
| cat_name, | |||||||
| o.name AS owner_name, | |||||||
| c.age | AS cat_age, | cat_name | owner_name | age | age | ||
| o.age | AS owner_age | Kitty | John Smith | 17 | 18 | ||
| FROM cat c | Sam | Danielle Davis | 5 | 10 | |||
| JOIN owner o | |||||||
| ON c.owner_id = o.id | |||||||
| AND c.age < o.age; | |||||||
Try out the interactive SQL JOINs course at LearnSQL.com, and check out our other SQL courses.
LearnSQL.com is owned by Vertabelo SA vertabelo.com | CC BY-NC-ND Vertabelo SA
Standard SQL Functions Cheat Sheet
TEXT FUNCTIONS
CONCATENATION
Use the || operator to concatenate two strings:
SELECT ‘Hi ‘ || ‘there!’;
— result: Hi there!
Remember that you can concatenate only character strings using
- . Use this trick for numbers:
SELECT ” || 4 || 2; — result: 42
Some databases implement non-standard solutions for concatenating strings like CONCAT() or CONCAT_WS() . Check the documentation for your specific database.
LIKE OPERATOR – PATTERN MATCHING
Use the character to replace any single character. Use the % character to replace any number of characters (including 0 characters).
Fetch all names that start with any letter followed by ‘atherine’ :
SELECT name
FROM names
WHERE name LIKE ‘_atherine’ ;
Fetch all names that end with ‘a’ :
SELECT name
FROM names
WHERE name LIKE ‘%a’ ;
USEFUL FUNCTIONS
Get the count of characters in a string: SELECT LENGTH( ‘LearnSQL.com’ ) ; — result: 12
Convert all letters to lowercase:
SELECT LOWER( ‘LEARNSQL.COM’ ) ; — result: learnsql.com
Convert all letters to uppercase:
SELECT UPPER( ‘LearnSQL.com’ ) ; — result: LEARNSQL.COM
Convert all letters to lowercase and all first letters to uppercase (not implemented in MySQL and SQL Server):
SELECT INITCAP( ‘edgar frank ted cODD’ ) ; — result: Edgar Frank Ted Codd
Get just a part of a string:
SELECT SUBSTRING( ‘LearnSQL.com’, 9) ; — result: .com
SELECT SUBSTRING( ‘LearnSQL.com’, 0, 6) ; — result: Learn
Replace part of a string:
SELECT REPLACE( ‘LearnSQL.com’, ‘SQL’, ‘Python’ ) ;
— result: LearnPython.com
NUMERIC FUNCTIONS
BASIC OPERATIONS
Use + – * / to do some basic math. To get the number of seconds in a week:
SELECT 60 * 60 * 24 * 7; — result: 604800
CASTING
From time to time, you need to change the type of a number. The CAST() function is there to help you out. It lets you change the type of value to almost anything (integer numeric double precision varchar, and many more).
Get the number as an integer (without rounding):
SELECT CAST( 1234.567 AS integer) ;
— result: 1234
Change a column type to double precision
SELECT CAST( column AS double precision) ;
USEFUL FUNCTIONS
Get the remainder of a division:
SELECT MOD( 13, 2) ;
— result: 1
Round a number to its nearest integer:
SELECT ROUND(1234.56789) ;
— result: 1235
Round a number to three decimal places: SELECT ROUND(1234.56789, 3) ; — result: 1234.568
PostgreSQL requires the first argument to be of the type numeric – cast the number when needed.
To round the number up:
SELECT CEIL( 13.1) ; — result: 14
SELECT CEIL( -13.9) ; — result: -13
The CEIL(x) function returns the smallest integer not less than x. In SQL Server, the function is called CEILING()
To round the number down:
SELECT FLOOR(13.8) ; — result: 13
SELECT FLOOR(-13.2) ; — result: -14
The FLOOR(x) function returns the greatest integer not greater than x
To round towards 0 irrespective of the sign of a number:
SELECT TRUNC(13.5) ; — result: 13
SELECT TRUNC(-13.5) ; — result: -13
TRUNC(x) works the same way as CAST(x AS integer) . In
MySQL, the function is called TRUNCATE()
To get the absolute value of a number:
SELECT ABS( -12 ) ; — result: 12
To get the square root of a number:
SELECT SQRT( 9) ; — result: 3
NULLs
To retrieve all rows with a missing value in the price column:
WHERE price IS NULL
To retrieve all rows with the weight column populated:
WHERE weight IS NOT NULL
Why shouldn’t you use price = NULL or weight != NULL? Because databases don’t know if those expressions are true or false – they are evaluated as NULLs.
Moreover, if you use a function or concatenation on a column that is NULL in some rows, then it will get propagated. Take a look:
domain LENGTH(domain)
LearnSQL.com 12
LearnPython.com 15
NULL NULL
vertabelo.com 13
USEFUL FUNCTIONS
COALESCE(x, y, …)
To replace NULL in a query with something meaningful:
SELECT
domain,
COALESCE(domain, ‘domain missing’ )
FROM contacts;
domain coalesce
LearnSQL.com LearnSQL.com
NULL domain missing
The COALESCE() function takes any number of arguments and returns the value of the first argument that isn’t NULL
NULLIF(x, y)
To save yourself from division by 0 errors:
SELECT
last_month,
this_month,
this_month * 100.0
- NULLIF(last_month, 0) AS better_by_percent
FROM video_views;
last_month this_month better_by_percent
| 723786 | 1085679 | 150.0 |
| 0 | 178123 | NULL |
The NULLIF(x, y) function will return NULL if x is the same as y, else it will return the x value.
CASE WHEN
The basic version of CASE WHEN checks if the values are equal (e.g., if fee is equal to 50, then ‘normal’ is returned). If there isn’t a matching value in the CASE WHEN, then the ELSE value will be returned (e.g., if fee is equal to 49, then ‘not available’ will show up.
SELECT
CASE fee
WHEN 50 THEN ‘normal’
WHEN 10 THEN ‘reduced’
WHEN 0 THEN ‘free’
ELSE ‘not available’
END AS tariff
FROM ticket_types;
The most popular type is the searched CASE WHEN – it lets you pass conditions (as you’d write them in the WHERE clause), evaluates them in order, then returns the value for the first condition met.
SELECT
CASE
WHEN score >= 90 THEN ‘A’
WHEN score > 60 THEN ‘B’
ELSE ‘F’
END AS grade
FROM test_results;
Here, all students who scored at least 90 will get an A, those with the score above 60 (and below 90) will get a B, and the rest will receive an F.
TROUBLESHOOTING
Integer division
When you don’t see the decimal places you expect, it means that you are dividing between two integers. Cast one to decimal: CAST(123 AS decimal) / 2
Division by 0
To avoid this error, make sure that the denominator is not equal to 0. You can use the NULLIF() function to replace 0 with a NULL, which will result in a NULL for the whole expression: count / NULLIF(count_all, 0 )
Inexact calculations
If you do calculations using real (floating point) numbers, you’ll end up with some inaccuracies. This is because this type is meant for scientific calculations such as calculating the velocity.
Whenever you need accuracy (such as dealing with monetary values), use the decimal / numeric type (or money if available).
Errors when rounding with a specified precision
Most databases won’t complain, but do check the documentation if they do. For example, if you want to specify the rounding precision in PostgreSQL, the value must be of the numeric type.
Try out the interactive Standard SQL Functions course at LearnSQL.com, and check out our other SQL courses.
LearnSQL.com is owned by Vertabelo SA vertabelo.com | CC BY-NC-ND Vertabelo SA
Standard SQL Functions Cheat Sheet
AGGREGATION AND GROUPING
COUNT(expr ) − the count of values for the rows within the
group
SUM(expr) − the sum of values within the group
AVG(expr) − the average value for the rows within the group
MIN(expr) − the minimum value within the group
MAX(expr) − the maximum value within the group
To get the number of rows in the table:
SELECT COUNT( *)
FROM city;
To get the number of non-NULL values in a column:
SELECT COUNT( rating)
FROM city;
To get the count of unique values in a column:
SELECT COUNT(DISTINCT country_id )
FROM city;
GROUP BY
CITY
name country_id
Paris 1
CITY
Marseille 1
country_id count
Lyon 1
1 3
Berlin 2
2 3
Hamburg 2
4 2
Munich 2
Warsaw 4
Cracow 4
The example above – the count of cities in each country:
SELECT name, COUNT(country_id)
FROM city
GROUP BY name;
The average rating for the city:
SELECT city_id, AVG( rating)
FROM ratings
GROUP BY city_id;
Common mistake: COUNT(*) and LEFT JOIN
When you join the tables like this: client LEFT JOIN project, and you want to get the number of projects for every client you know, COUNT(*) will return 1 for each client even if you’ve never worked for them. This is because, they’re still present in the list but with the NULL in the fields related to the project after the JOIN. To get the correct count (0 for the clients you’ve never worked for), count the values in a column of the other table, e.g., COUNT(project_name) . Check out this exercise to see an example.
DATE AND TIME
There are 3 main time-related types: date time, and timestamp. Time is expressed using a 24-hour clock, and it can be as vague as just hour and minutes (e.g., 15:30 – 3:30 p.m.) or as precise as microseconds and time zone (as shown below):
2021-12-31 14:39:53.662522-05
date time
timestamp
YYYY-mm-dd HH:MM:SS.ssssss±TZ
14:39:53.662522-05 is almost 2:40 p.m. CDT (e.g., in Chicago; in UTC it’d be 7:40 p.m.). The letters in the above example represent:
In the date part: In the time part:
YYYY – the 4-digit HH – the zero-padded hour in a 24-
year. hour clock.
mm – the zero-padded MM – the minutes.
month (01—January SS – the seconds. Omissible
through 12 ssssss – the smaller parts of a
December). second – they can be expressed
dd – the zero-padded using 1 to 6 digits. Omissible
day. ±TZ – the timezone. It must start
with either + or -, and use two
digits relative to UTC. Omissible
What time is it?
To answer that question in SQL, you can use:
CURRENT_TIME – to find what time it is.
CURRENT_DATE – to get today’s date. (GETDATE() in SQL Server.)
CURRENT_TIMESTAMP – to get the timestamp with the two above.
Creating values
To create a date, time, or timestamp, simply write the value as a string and cast it to the proper type.
SELECT CAST( ‘2021-12-31′ AS date) ; SELECT CAST( ’15:31’ AS time) ;
SELECT CAST( ‘2021-12-31 23:59:29+02′ AS timestamp ) ;
SELECT CAST( ’15:31.124769′ AS time ) ;
Be careful with the last example – it will be interpreted as 15 minutes 31 seconds and 124769 microseconds! It is always a good idea to write 00 explicitly for hours: ’00:15:31.124769′
You might skip casting in simple conditions – the database will know what you mean.
SELECT airline, flight_number, departure_time FROM airport_schedule
WHERE departure_time < ’12:00’;
INTERVALs
Note: In SQL Server, intervals aren’t implemented – use the DATEADD() and DATEDIFF() functions.
To get the simplest interval, subtract one time value from another:
SELECT CAST( ‘2021-12-31 23:59:59’ AS timestamp) – CAST( ‘2021-06-01 12:00:00’ AS timestamp) ;
— result: 213 days 11:59:59
To define an interval: INTERVAL ‘1’ DAY
This syntax consists of three elements: the INTERVAL keyword, a quoted value, and a time part keyword (in singular form.) You can use the following time parts: YEAR MONTH WEEK DAY HOUR MINUTE, and SECOND. In MySQL, omit the quotes. You can join many different INTERVALs using the + or operator:
INTERVAL ‘1’ YEAR + INTERVAL ‘3’ MONTH
In some databases, there’s an easier way to get the above value. And it accepts plural forms! INTERVAL ‘1 year 3 months’
There are two more syntaxes in the Standard SQL:
Syntax What it does
INTERVAL ‘x-y’ YEAR TO INTERVAL ‘x year y
MONTH month’
INTERVAL ‘x-y’ DAY TO INTERVAL ‘x day y
SECOND second’
In MySQL, write year_month instead of YEAR TO MONTH and day_second instead of DAY TO SECOND.
To get the last day of a month, add one month and subtract one day:
SELECT CAST( ‘2021-02-01’ AS date )
- INTERVAL ‘1’ MONTH – INTERVAL ‘1’ DAY ;
To get all events for next three months from today:
SELECT event_date, event_name FROM calendar
WHERE event_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL ‘3’ MONTH ;
To get part of the date:
SELECT EXTRACT(YEAR FROM birthday)
FROM artists;
One of possible returned values: 1946. In SQL Server, use the DATEPART(part, date) function.
TIME ZONES
In the SQL Standard, the date type can’t have an associated time zone, but the time and timestamp types can. In the real world, time zones have little meaning without the date, as the offset can vary through the year because of daylight saving time. So, it’s best to work with the timestamp values.
When working with the type timestamp with time zone (abbr. timestamptz), you can type in the value in your local time zone, and it’ll get converted to the UTC time zone as it is inserted into the table. Later when you select from the table it gets converted back to your local time zone. This is immune to time zone changes.
AT TIME ZONE
To operate between different time zones, use the AT TIME ZONE keyword.
If you use this format: {timestamp without time zone} AT TIME ZONE {time zone} , then the database will read the time stamp in the specified time zone and convert it to the time zone local to the display. It returns the time in the format timestamp with time zone.
If you use this format: {timestamp with time zone} AT
TIME ZONE {time zone}, then the database will convert the time in one time zone to the target time zone specified by AT TIME ZONE. It returns the time in the format timestamp without time zone, in the target time zone.
You can define the time zone with popular shortcuts like UTC, MST, or GMT, or by continent/city such as: America/New_York, Europe/London, and Asia/Tokyo
Examples
We set the local time zone to ‘America/New_York’ .
SELECT TIMESTAMP ‘2021-07-16 21:00:00’ AT TIME ZONE ‘America/Los_Angeles’;
— result: 2021-07-17 00:00:00-04
Here, the database takes a timestamp without a time zone and it’s told it’s in Los Angeles time, which is then converted to the local time – New York for displaying. This answers the question “At what time should I turn on the TV if the show starts at 9 PM in Los Angeles?”
SELECT TIMESTAMP WITH TIME ZONE ‘2021-06-20 19:30:00’ AT TIME ZONE ‘Australia/Sydney’; — result: 2021-06-21 09:30:00
Here, the database gets a timestamp specified in the local time zone and converts it to the time in Sydney (note that it didn’t return a time zone.) This answers the question “What time is it in Sydney if it’s 7:30 PM here?”
Try out the interactive Standard SQL Functions course at LearnSQL.com, and check out our other SQL courses.
LearnSQL.com is owned by Vertabelo SA vertabelo.com | CC BY-NC-ND Vertabelo SAsql