Link: SQL
Select, From, Where
FROM
specify the tableSELECT
apply to columns.- My note:
SELECT
is different fromSelect
in R. It does not mean selecting column xyz and only xyz will be loaded. In SQL it means xyz are the columns that would appear in the final dataset! In addition, all columns included inSELECT
must be passed through Group by, Having, Count clause if it exists.
- My note:
WHERE
apply to rows (filter), see more in SQL filter: WHEREWHERE...LIKE
: filter. We can also use wildcard%
for any number of characters.
query = """
SELECT Name, Type
FROM `bigquery-public-data.pet_records.pet`
WHERE Animal = "Cat"
"""
-- Example for WHERE LIKE
WHERE Name LIKE "Ripley"
WHERE Name like '%ipl%'
-- Select all columns
SELECT * FROM table1
-- Select as alias
SELECT `by` AS author FROM table1
Alias for SELECT … FROM
We can use alias for selected table too:
SELECT
g.book_title
FROM goodreads AS g;
SELECT DISTINCT
Often combine with SELECT
: SELECT DISTINCT
DISTINCT multiple columns
If two ore more columns in SELECT DISTINCT
: use comma to separate
SELECT DISTINCT user_id, status FROM trades