SQL JOIN

Question 1

How to make CROSS JOIN connection between 2 tables?

Answer 1

SELECT * FROM Customers CROSS JOIN Categories;

Question 2

How to make CROSS JOIN connection and show only 2 columns from each table?

Answer 2

SELECT cust.CustomerName, categor.CategoryName FROM Customers AS cust CROSS JOIN Categories AS categor;

Question 3

How to make INNER JOIN connection?

Answer 3

SELECT * FROM Products INNER JOIN Categories ON Categories.CategoryID = Products.CategoryID;

Question 4

How to make NATURAL JOIN connection?

Answer 4

# we use natural join if we have the same fields in two tables
SELECT * FROM Products NATURAL JOIN Categories;

Question 5

How to make LEFT OUTER JOIN?

Answer 5

SELECT * FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID;

Question 6

How to make RIGHT OUTER JOIN?

Answer 6

SELECT * FROM Orders
RIGHT OUTER JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID;

Question 7

How to combine result of 2 selects into one? ( sum of 2 tables)

Answer 7

# all cities from the first table + all cities from another table
# values will be UNIQ
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;

Question 8

How to combine result of 2 selects into one with duplication? ( sum of 2 tables)

Answer 8

# all cities from the first table + all cities from another table
# values won't be UNIQ
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;

Question 9

How to return data from the first request which are also included in another request?

Answer 9

SELECT City FROM Customers
INTERSECT
SELECT City FROM Suppliers;

Question 10

How to return data from the first request but without the data from the second request?

Answer 10

SELECT City FROM Customers
EXCEPT
SELECT City FROM Suppliers;

Question 11

How to create nested join in rails?

Answer 11

Building ( has_many :floors)
Floor ( has many :cabinets)
Cabinet ( belongs_to floor)

to get access from cabinet to building

Cabinet.joins(:floors => buildings)

you can nest more than one join and do it in 2 directions

AvailableCurrency.joins(scheme: {companies: :employees}).where(‘employees.id = 1’)

Question 12

How to return data from joined table?

Answer 12

> > cities = RefCity.joins(:country).select(‘ref_cities.id, ref_cities.name’)
cities.first.id # city id
cities.first.name # city name