Skip to content

Instantly share code, notes, and snippets.

@JamesYang76

JamesYang76/active_record.md

Last active April 16, 2020 02:21
Show Gist options
  • Save JamesYang76/e6d06a7d2161d7775a37f84640797677 to your computer and use it in GitHub Desktop.
Save JamesYang76/e6d06a7d2161d7775a37f84640797677 to your computer and use it in GitHub Desktop.
Download ZIP
ActiveRecord
Raw
active_record.md

supported type

:binary
:boolean
:date
:datetime
:decimal
:float
:integer
:primary_key
:string
:text
:time
:timestamp

# refer to rails generate model -h 
# http://blog.ifyouseewendy.com/blog/2015/08/10/data-types-in-rails/
rails console> ActiveRecord::Base.connection.native_database_types.keys

ActiveRecord::Relation

ActiveRecord::Relation object allows us to chain queries together and this Relation class is at the heart of the new query syntax.
Finder methods that return a collection, such as where and group, return an instance of ActiveRecord::Relation. Methods that find a single entity, such as find and first, return a single instance of the model.

Product.find([1,2]).class
==> Array
 Product.where(id:[1,2]).class
==>Product::ActiveRecord_Relation

Product.where(id:[1,2]).to_a.class
 => Array

Association

When a table has a reference of other table(foreign key), the model has belongs_to Association

create_table "groups", force: :cascade do |t|
  t.string "name"
  t.bigint "user_id", null: false
  t.index ["user_id"], name: "index_groups_on_user_id"
end

create_table "users", force: :cascade do |t|
  t.string "name", default: "", null: false
  t.string "email", default: "", null: false
end  

class Group < ApplicationRecord
  belongs_to :user
end
class User < ApplicationRecord
  has_many :groups, dependent: :destroy
end

table and model name

The model class name should follow the Ruby conventions, using the CamelCase form, while the table name must contain the words separated by underscores

# model     table
Article	    articles
LineItem    line_items
Deer        deers
Mouse       mice
Person      people

typical typo errors in query

  • joins/includes method, always use the exact same name as the relation
  • In the where clauses, always use the pluralized name of the relation (actually, the table's name, which is by default the model name in plural but can also be manually set)
User has_many :posts
Post belongs_to :user

# Usage of joins/includes & where:
User.includes(:posts).where(posts: { name: 'BlogPost #1' })
                  #^            ^
Post.joins(:user).where(users: { name: 'Little Boby Table' })
              #^^           ^

joins

joins using INNER JOIN

# Multiple joins:
Article.joins(:category, :comments)
SELECT articles.* FROM articles
  INNER JOIN categories ON categories.id = articles.category_id
  INNER JOIN comments ON comments.article_id = articles.id


Article.joins(comments: :guest)
SELECT articles.* FROM articles
  INNER JOIN comments ON comments.article_id = articles.id
  INNER JOIN guests ON guests.comment_id = comments.id
  

Category.joins(articles: [{ comments: :guest }, :tags])
SELECT categories.* FROM categories
  INNER JOIN articles ON articles.category_id = categories.id
  INNER JOIN comments ON comments.article_id = articles.id
  INNER JOIN guests ON guests.comment_id = comments.id
  INNER JOIN tags ON tags.article_id = articles.id

User.joins("LEFT JOIN bookmarks ON bookmarks.bookmarkable_type = 'Post' AND bookmarks.user_id = users.id")
SELECT "users".* FROM "users" LEFT JOIN bookmarks ON bookmarks.bookmarkable_type = 'Post' AND bookmarks.user_id = users.id

includes

 includes will either use a separate query (like above) or a LEFT OUTER JOIN. If you have a where or order clause that references a relationship, a LEFT OUTER JOIN is used versus a separate query.

Person.
  includes(:manager).
  references(:manager).
  where(
    "managers_people.id != ? OR managers_people.id IS NULL",
    Person.find_by!(name: "Eve")
  )
=> SELECT "people"."id" AS t0_r0, "people"."name" AS t0_r1, "people"."role_id" AS t0_r2,
"people"."location_id" AS t0_r3, "people"."manager_id" AS t0_r4, "managers_people"."id" AS t1_r0,
"managers_people"."name" AS t1_r1, "managers_people".role_id AS t1_r2,
"managers_people"."location_id" AS t1_r3, "managers_people"."manager_id" AS t1_r4
FROM "people"
LEFT OUTER JOIN "people" "managers_people"
  ON "managers_people"."id" = "people"."managers_id"
WHERE ("managers_people"."id" != 1 OR "managers_people"."id" IS NULL)

where

# Rails Joins Association Condition
Product.joins(:store).where(stores: {title: "Apple"})
Product.joins(:store).where("stores.title = 'Apple'")


Book.where("title ILIKE ?", "%#{params[:q]}%")

belong_to

belongs_to associations are now automatically required: true
All model's belongs_to associations are now treated as if the required: true option is present.
So if the association is optional, that must be declared, or validation will fail without the presence of the associated model record.

class Post < ApplicationRecord
  belongs_to :user, optional: true
end

post = Post.create(title: 'Hi')
=> <Post id: 2, title: "Hi", user_id: nil>


Rails.application.config.active_record.belongs_to_required_by_default = false

class Post < ApplicationRecord
  belongs_to :user
end

post = Post.create(title: 'Hi')
=> <Post id: 3, title: "Hi", user_id: nil, created_at: "2016-02-11 12:36:05", updated_at: "2016-02-11 12:36:05">
class Person < ActiveRecord::Base
  belongs_to :role
end

# roles have a name and a boolean column called billable.
class Role < ActiveRecord::Base
  has_many :people
end

# We're hitting the database potentially hundreds or thousands or millions of times (for each person, we're making a request to the roles table)
Person.all.select { |person| person.role.billable? }
Person.all.joins(:role).where(roles: { billable: true })
#------------------------------------------------------------

class Role < ActiveRecord::Base
  def self.billable
    where(billable: true)
  end
end
Person.joins(:role).merge(Role.billable)

class Person < ActiveRecord::Base
  def self.billable
    joins(:role).merge(Role.billable)
  end
end

Person.billable

SELECT "people".*
FROM "people"
INNER JOIN "roles"
  ON "roles.id" = "people"."role_id"
WHERE "roles"."billable" = 't';

has_many

class Person < ActiveRecord::Base
  belongs_to :location
  belongs_to :role
end

class Role < ActiveRecord::Base
  has_many :people
end

class Location < ActiveRecord::Base
  has_many :people
  belongs_to :region
end

class Region < ActiveRecord::Base
  has_many :locations
end

# data could be duplicated because of has_many
Location.joins(people: :role).where(roles: { billable: true })

Location.joins(people: :role).where(roles: { billable: true }).distinct
# SELECT DISTINCT "locations".*
# FROM "locations"
# INNER JOIN "people"
#   ON "people"."location_id" = "locations"."id"
# INNER JOIN "roles"
#   ON "roles"."id" = "people"."role_id"
# WHERE "roles"."billable" = 't';

# order by region name, then by location name
Location.joins(:region).merge(Region.order(:name)).order(:name)
# SELECT "locations".*
# FROM "locations"
# INNER JOIN "regions"
#  ON "regions"."id" = "locations"."region_id"
# ORDER BY "regions"."name" ASC, "locations"."name" ASC;

# --------------------------

class Location < ActiveRecord::Base
  has_many :people
  
  def self.billable
    joins(people: :role).where(roles: { billable: true }).distinct
  end
  
  def self.by_region_and_location_name
    joins(:region).merge(Region.order(:name)).order(:name)
  end
end

# order the billable locations by region name, then by location name
Location.billable.by_region_and_location_name

# SELECT DISTINCT "locations".*
# FROM "locations"
# INNER JOIN "people"
#   ON "people"."location_id" = "locations"."id"
# INNER JOIN "roles"
#   ON "roles"."id" = "people"."role_id"
# INNER JOIN "regions"
#   ON "regions"."id" = "locations"."region_id"
# WHERE "roles"."billable" = 't'
# ORDER BY "regions"."name" ASC, "locations"."name" ASC;

# PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must
# appear in select list
# LINE 1: ...gion_id" WHERE "roles"."billable" = 't'  ORDER BY "regions"

# it's an order of operations issue: we have to make sure that when we eliminate some rows with DISTINCT
# we don't lose important information that we needed for ordering. 
# So we need to be explicit about how to end up with distinct things first, and then order them.

Location.from(Location.billable, :locations)
# SELECT "locations".*
# FROM (
#   SELECT DISTINCT "locations".*
#   FROM "locations"
#   INNER JOIN "people"
#     ON "people"."location_id" = "locations"."id"
#   INNER JOIN "roles"
#     ON "roles"."id" = "people"."role_id"
#   WHERE "roles"."billable" = 't'
# ) locations;

Location.from(Location.billable, :locations).by_region_and_location_name
# SELECT "locations".*
# FROM (
#  SELECT DISTINCT "locations".*
#  FROM "locations"
#  INNER JOIN "people"
#    ON "people"."location_id" = "locations"."id"
#  INNER JOIN "roles"
#    ON "roles"."id" = "people"."role_id"
#  WHERE "roles"."billable" = 't'
# ) locations
# INNER JOIN "regions"
#  ON "regions"."id" = "locations"."region_id"
# ORDER BY "regions"."name" ASC, "locations"."name" ASC;

self join

# optional: true because manager_id could be nil
# belongs_to will now trigger a validation error by default if the association is not present.
# We can turn this off on a per-association basis with optional: true

class Person < ApplicationRecord
  belongs_to :manager, class_name: "Person", optional: true
  has_many :employees, class_name: "Person", foreign_key: :manager_id
end

class CreatePeople < ActiveRecord::Migration[6.0]
  def change
    create_table :people do |t|
      t.references :manager
      t.string "name", null: false
      t.timestamps
    end
  end
end


Person.joins(:manager)
# ActiveRecord made up the alias managers_people

# SELECT "people".*
# FROM "people"
# INNER JOIN "people" "managers_people"
#  ON "managers_people"."id" = "people"."manager_id"

custom joins

Person.joins(:manager).where.not(managers_people: { id: Person.find_by!(name: "Eve") })
Person.
  joins(<<~SQL).
    LEFT JOIN people managers
    ON managers.id = people.manager_id
  SQL
  where.
  not(managers: { id: Person.find_by!(name: "Eve") })

# find all distinct locations with at least one person who belongs to a billable role, ordered by region name, then location name
Location.
  from(
    Location.
      joins(people: :role).
      where(roles: { billable: true }).
      distinct,
    :locations
  ).
  joins(:region).
  merge(Region.order(:name)).
  order(:name)

# SELECT "locations".*
# FROM (
#  SELECT DISTINCT "locations".*
#  FROM "locations"
#  INNER JOIN "people"
#    ON "people"."location_id" = "locations"."id"
#  INNER JOIN "roles"
#    ON "roles"."id" = "people"."role_id"
#  WHERE "roles"."billable" = 't'
# ) locations
# INNER JOIN "regions"
#   ON "regions"."id" = "locations"."region_id"
# ORDER BY "regions"."name" ASC, "locations"."name" ASC;

Location.
  joins(
    "INNER JOIN (" +
      Location.
        joins(people: :role).
        where(roles: { billable: true }).
        distinct.
        to_sql +
      ") billable_locations " \
      "ON locations.id = billable_locations.id"
  ).
  joins(:region).
  merge(Region.order(:name)).
  order(:name)  
  
# SELECT "locations".*
# FROM "locations"
# INNER JOIN "regions"
#   ON "regions"."id" = "locations"."region_id"
# INNER JOIN (
#   SELECT DISTINCT "locations".*
#   FROM "locations"
#   INNER JOIN "people"
#     ON "people"."location_id" = "locations"."id"
#   INNER JOIN "roles"
#     ON "roles"."id" = "people"."role_id"
#   WHERE "roles"."billable" = 't'
# ) billable_locations
#   ON "locations"."id" = "billable_locations"."id"
# ORDER BY "regions"."name" ASC, "locations"."name" ASC;

Querying with Aggregations

It's important to realize that the return value when you use group is a hash, with the keys being the unique values you are grouping by, and the values are the aggregation. count, sum, average, etc just returned scalar values.

Product.select("stores.name, average(:price)").joins(:store).group("stores.name").average(:price)
# SELECT AVG("products"."price") AS average_price, stores.name AS stores_name 
# FROM "products" INNER JOIN "stores" ON "stores"."id" = "products"."store_id" GROUP BY stores.name
=> {"Samsung"=>0.7e4, "Apple"=>0.3e4}

# since the :employees association is not named conventionally (i.e., it's not named after the model),
# ActiveRecord makes up an alias for the table that it can guarantee won't be ambiguous.
Person.joins(:employees).group("people.name").count("employees_people.id")
# SELECT people.name, COUNT(employees_people.id)
# FROM "people"
# INNER JOIN "people" "employees_people"
#   ON "employees_people"."manager_id" = "people"."id"
# GROUP BY people.name;


Person.
  joins("LEFT JOIN people employees ON employees.manager_id = people.id").
  group("people.name").
  count("employees.id")

# SELECT people.name, COUNT(employees.id)
# FROM "people"
# LEFT JOIN people employees
#  ON employees.manager_id = people.id GROUP BY people.name;
  
# find people with lower than average salaries at their location:  
Person.
  joins(
    "INNER JOIN (" +
      Person.
        select("location_id, AVG(salary) as average").
        group("location_id").
        to_sql
    ") salaries " \
    "ON salaries.location_id = people.location_id"
  ).
  where("people.salary < salaries.average")
# SELECT "people".*
# FROM "people"
# INNER JOIN (
#   SELECT location_id, AVG(salary) as average
#   FROM "people"
#   GROUP BY "people"."location_id"
# ) salaries
#   ON salaries.location_id = people.location_id
# WHERE (people.salary < salaries.average)

float vs decimal vs money

# integer   Integer - store positive or negative integers
# float     Float	-  store decimal places
# decimal   BigDecimal -	stores decimal places more precisely than float (e.g. for currency)
# money     BigDecimal

# precision and scale only can be applied on decimal
# ArgumentError: Error adding decimal column: precision cannot be empty if scale is specified
# money is always with scale: 2, and user cannot apply scale and precision

# If scale is applied on a attribute, attribute is automatically round up.
# for example, price attritue is money type
proudct.price = 0.249
proudct.price
=>0.249
proudct.save!
proudct = Product.last
proudct.price
=>0.25e0

#decimal type with scale, round up instantly happen
# if decima_price is decimal type with scale: 2
proudct.decima_price = 0.249
proudct.decima_price
=>0.25e0

# do not save string directly with commma, $
Product.create(price:"10,000")
=>0.1e2

#find the highest price product, ordered by name
Product.order(price: :desc).limit(3).sort_by(&:name)
Product.order(price: :desc).limit(3).sort_by(&:name).reverse
# this is using ruby sort_by

Product.
  joins(
    "INNER JOIN (" +
      Product.select("id, rank() OVER (ORDER BY price DESC)").to_sql + 
    ") prices ON prices.id = products.id"
  ).where("prices.rank <= 3").order(:name)

# SELECT "products".* FROM "products" 
# INNER JOIN (SELECT id, rank() OVER (ORDER BY price DESC) FROM "products") prices 
# ON prices.id = products.id WHERE (prices.rank <= 3) 
# ORDER BY "products"."name" ASC

persisted? vs new_record?

Returns true if the record is persisted, i.e. it’s not a new record

u=User.new
u.new_record? => true
u.persisted? => false
u = User.findbyemail('[email protected]')
u.persisted? => true

create

# Create a single new object
User.create(:first_name => 'Jamie')

# Create an Array of new objects
User.create([{ :first_name => 'Jamie' }, { :first_name => 'Jeremy' }])

# Create a single object and pass it into a block to set other attributes.
User.create(:first_name => 'Jamie') do |u|
  u.is_admin = false
end

# Creating an Array of new objects using a block, where the block is executed for each object:
User.create([{ :first_name => 'Jamie' }, { :first_name => 'Jeremy' }]) do |u|
  u.is_admin = false
end

<<

automatically save into database

# save product to database
st.products << Product.new(title:"test", price:23.54)

pluck vs select

pluck returns array, but select returns ActiveRecord::Relation

Product.all.pluck(:id)
 => [1, 2]

Product.select(:id)
 => #<ActiveRecord::Relation [#<Product id: 1>, #<Product id: 2>]> 
 
p = Product.select("price + price as dobule_price").last
p.dobule_price
=>  0.21e2

#it can be also used just like Array#select. the return is not Relation, but Array
 Product.select { |p| p.title != nil }
=>  [#<Product id: 1, price: nil, price_precision: nil...>]
Product.select(&:title)

pluck vs map

Product.all.pluck(:id)
SELECT "products"."id" FROM "products"
 => [1, 2]
 
Product.all.map(&:id)
SELECT "products".* FROM "products"
 => [1, 2]

to hash

Product.all.map { |p| p.slice(:id,:title) }
SELECT "products".* FROM "products"
=> [{"id"=>123, "title"=>"Chicken"}, {"id"=>127, "title"=>"Chicken"},..]

Product.pluck(:id, :title).map { |id, title| {id: id, title: title}}
SELECT "products"."id", "products"."title" FROM "products"
=> [{"id"=>123, "title"=>"Chicken"}, {"id"=>127, "title"=>"Chicken"},..]

Product.all.as_json only:[:id, :title]
=> [{"id"=>123, "title"=>"Chicken"}, {"id"=>127, "title"=>"Chicken"},..]

Product.first.attributes
 => {"id"=>121,"price"=>0.3e4,...}
Product.first.as_json
=> {"id"=>121,"price"=>"3000.0",...}

Product.select(:price).group(:price).map(&:attributes)
 => [{"id"=>nil, "price"=>0.3e4}, {"id"=>nil, "price"=>0.8e4}, {"id"=>nil, "price"=>0.6e4}]

Refer : https://zaiste.net/rails_pluck_to_hash/

Use counter_cache

size or count causes database query in the the associated records.
counter_cache prevents n+1 query.

# Bad smell
Article.all.each do |article|
  article.responses.size ## => will lead to a SELECT COUNT(*) query
end 

# Use counter_cache
class Response
  belongs_to :article, counter_cache: true
end

$ rails generate migration AddResponsesCountToArticles responses_count:integer

article.responses.size ##=> There is no query

https://blog.appsignal.com/2018/06/19/activerecords-counter-cache.html

binding variables

 Product.where("title = ? AND price = ?", "car", 10)
 Product.where("title = :name AND price = :value", name: "car", value: 10)
 Product.where(title: "car", price:10)
 
 product = Product.select("*", "CAST(money_price as decimal) + price as total_price").first
 product.total_price
 
 Store.select("products.id, products.title").joins(:products)
 # SELECT products.id, products.title FROM "stores" INNER JOIN "products" ON "products"."store_id" = "stores"."id"

Automagical ?

Automagical ?-methods for boolean attributes in ActiveRecord

create_table "users", :force => true do |t|
  t.boolean  "email_confirmed"
end

# you may now say:
User.email_confirmed?

DB Diagram

gem 'rails-erd', group: :development
brew install graphviz
bundle exec erd

Reference

https://thoughtbot.com/upcase/videos/advanced-querying-custom-joins

Raw
sql.md

Joins

There are data in database

[
  #<User id: 2, name: "Jane Roe", email: "[email protected]">, 
  #<User id: 3, name: "test", email: "[email protected]">
]

[
  #<Group id: 3, name: "Friend", user_id: 2>,
  #<Group id: 4, name: "Company", user_id: 2>,
  #<Group id: 5, name: "Home", user_id: 2>
 ]

Inner join

SELECT "users".* FROM "users" INNER JOIN "groups" ON "groups"."user_id" = "users"."id"
[
    #<User id: 2, name: "Jane Roe", email: "[email protected]">, 
    #<User id: 2, name: "Jane Roe", email: "[email protected]">, 
    #<User id: 2, name: "Jane Roe", email: "[email protected]">
]

Left join

SELECT "users".* FROM "users" LEFT OUTER JOIN "groups" ON "groups"."user_id" = "users"."id" 
[
    #<User id: 2, name: "Jane Roe", email: "[email protected]">, 
    #<User id: 2, name: "Jane Roe", email: "[email protected]">, 
    #<User id: 2, name: "Jane Roe", email: "[email protected]">,
    #<User id: 3, name: "test", email: "[email protected]">
]

Join vs Where

  • JOIN data in ON
  • Filter data in WHERE
# All three of these queries produce the same correct result
# shoud use
SELECT *
FROM facebook
JOIN linkedin
ON facebook.name = linkedin.name

# bad smell
SELECT *
FROM facebook
JOIN linkedin
WHERE facebook.name = linkedin.name

# implicit JOIN
# bad smell
SELECT *
FROM facebook, linkedin
WHERE facebook.name = linkedin.name

Blow query result are different.

SELECT *
FROM facebook
LEFT JOIN linkedin
ON facebook.name = linkedin.name
WHERE facebook.city = 'SF'

# after join
# facebook.name  facebook.city linkedin.name  linkedin.city
# Matt           SF            Matt            SF
# Lisa           NY 
# Jaff           NY
# Dave           LA            Dave            LA

# afer where
# facebook.name  facebook.city linkedin.name  linkedin.city
# Matt           SF            Matt            SF


SELECT *
FROM facebook
LEFT JOIN linkedin
ON facebook.name = linkedin.name AND facebook.city = 'SF'

# facebook.name  facebook.city linkedin.name  linkedin.city
# Matt           SF            Matt            SF
# Lisa           NY 
# Jaff           NY
# Dave           LA

Rerence : https://dataschool.com/how-to-teach-people-sql/difference-between-where-and-on-in-sql/

NULL

1 = NULL is NULL also 1 != NULL is NULL

IS NUL

SELECT "products".* FROM "products" WHERE "products"."title" IS NULL

IS NOT NULL

SELECT "products".* FROM "products" WHERE "products"."title" IS NOT NULL

Grouping

Group By

The GROUP BY statement groups rows that have the same values into summary rows, which each summary rows should be one.

# Title    Price
# house	   100.00
# phone	   100.00
# Chicken  100.00
# Chicken  50.00
# car      100.00

SELECT  price FROM products Group By price;
# Price
# 50.00
# 100.00

SELECT  price, count(*) FROM products Group By price;
# Price   count
# 50.00   4
# 100.00  1

# there’s no way for the database engine to select a title since there could be more than one record with the same price.
# there could be several titles on each rows.
SELECT  title, price, count(*) FROM products Group By price;
# ERROR: column "products.title" must appear in the GROUP BY clause or be used in an aggregate function

SELECT  title, price, count(*) FROM products Group By title, price;
# Title   Price    Count
# car     100.00   1
# phone   100.00   1
# house   100.00   1
# Chicken 50.00    1
# Chicken 100.00   1

SELECT  string_agg(title, ', ') as titles, price, count(*) FROM products Group By price;
# Title                         Price    Count
# Chicken, phone, car, house    100.00   4
# Chicken                       100.00   1  

# Because id is unique, there is no error
SELECT  id, title, price  FROM products Group By id;
# Id   Title     Price
# 121  house     100.00
# 122  phone     100.00
# 123  Chicken   100.00
# 124  car       100.00
# 127  Chicken	 50.00

Reference: https://medium.com/@riccardoodone/the-love-hate-relationship-between-select-and-group-by-in-sql-4957b2a70229

having

It should be used with group by, and it is selects rows after grouping
Because where keyword could not be used with aggregate functions, it was added.

select s.title, AVG(price) from products join stores s on products.store_id = s.id
group by s.title;
# title   avg
# E-mart	100
# KFC     50

select s.title, AVG(price) from products join stores s on products.store_id = s.id
group by s.title
having  AVG(price) >= 100;
# title   avg
# E-mart	100

subquery

SELECT

SELECT id,name,price,(SELECT AVG(price) FROM products) AS AvgListPrice
FROM products;

# performance problem because the subquery in the first example will be executed once every row

FROM

SELECT AVG(product_count) from (
SELECT  stores.title, count(products.id) as product_count
FROM stores
JOIN products
ON stores.id = products.store_id
Group By stores.title) t;

join

select products.id,products.name, products.price, average
from products
    join (
        select store_id, AVG(price) as average
        from products
        GROUP BY store_id
    )price
    on price.store_id = products.store_id
where (products.price >= price.average);

WHERE

EXISTS

The EXISTS operator returns true if the subquery returns at least one record and false if no row is selected. The database engine does not have to run the subquery entirely. Thefore, the results are not duplicated

SELECT * FROM users WHERE exists(
    SELECT * from groups WHERE groups.user_id = users.id);
    
# id    name         email
# 2     Jane Roe     [email protected]


# This is wrong, Returens whole users or nothing depending on group has 'Rest' or not
SELECT * FROM users WHERE exists(
      SELECT * from groups WHERE name = 'Rest' );
IN

The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.

SELECT id, title from stores where id IN (
    SELECT store_id from products
    );
ANY vs ALL
  • The ANY and ALL operators are used with a WHERE or HAVING clause.
  • The ANY operator returns true if any of the subquery values meet the condition.
  • The ALL operator returns true if all of the subquery values meet the condition.
  • The results are not duplicated
SELECT AVG(price)
from products
group by store_id;

# store_id   avg
# 7	         300
# 6	         1450

# all records which price is greater than or equal to 300 or 1450
SELECT title,price from products where price >= ANY (
    SELECT AVG(price)
    from products
    group by store_id
);

# all records which price is greater or equal to 1450 
SELECT title,price from products where price >= ALL (
    SELECT AVG(price)
    from products
    group by store_id
);

rank

select products.name, products.price,
       rank() over(order by price DESC) price_rank
from products
ORDER BY price DESC;
# name        price price_rank
# Mac book	  8000  1
# Iphone	    6000	2
# Iphone	    3000	3
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment