Cartesian(Cross) Joins , Inner Joins and Self Joins

Cartesian(Cross) Joins :

if the first table contains x rows and second table contains y rows then the end result will be of x*y,
it dont match on anything hence the end result may be incorrect and meaningless , also the output is too heavy.
example :

SELECT product_name
 ,unit_price
 ,company_name
FROM suppliers
CROSS JOIN products;

Inner Joins :

Inner join keyword selects records that have matching values in both tables.

SELECT suppliers.companyName
 ,ProductName
 ,UnitPrice
FROM suppliers
INNER JOIN products ON 

suppliers.supplierid = products.supplierid;

--a thing to notice is that i used a prequalifier suppliers.companyName as both tables had a column names companyName.

Inner Join with multiple table :

>>SELECT o.Orderid

 ,c.companyname
 ,e.lastname
FROM (
 (
  orders o INNER JOIN customers c ON o.customerid = c.customerid
  ) INNER JOIN employees e ON o.employeeid = e.employeeid
 );

Self Join:

We can actually join a table to itself. 

>>SELECT a.customer_name AS customer1
 ,b.customer_name AS customer2
 ,a.city
FROM customer a
 ,customer b
WHERE a.customerid = b.customerid
 AND a.city = b.city
ORDER BY a.city;