Does this explain all the joins correctly?

1

Please help me before an interview.
An inner join means we are only getting rows that match the = criteria. So a customer_master _table with customer_Id will bring rows in the Orders_table that has a customer_id match. If there orders has 100 rows it will bring all 100.
A left outer join, will bring also orders rows that do not have a customer_id match from the customer so if customer 002 has no orders, it will bring one row from left side.
a right join will bring orders that have no customer id in the customer master.
A full outer join will bring all rows from both.
Cross join is the same as full outer join.

2

An inner join means we are only getting rows that match the = criteria.

Correct.
Edit: see ScottPletcher's reply for a totally correct answer.

SELECT C.customer_id
           O.order_id
FROM customers as C
    INNER JOIN orders as O
        ON C.customer_id = O.customer_id

When the customers table has 100 records and 2 customers have never made an order yet, those two customers will not be present in the result set.
Basically: the result set will contain as many records per customer as (s)he made orders. That could be 0, 1, 2, ...

A left outer join, will bring also orders rows that do not have a customer_id match from the customer so if customer 002 has no orders, it will bring one row from left side.

A left outer join, will bring also orders rows that do not have a customer_id match from the customer

This part is confusing. I would rewrite it as:

A left outer join, will also return customer rows that do not have a customer_id match in the orders table

so if customer 002 has no orders, it will bring one row from left side.

This is correct

SELECT C.customer_id
           O.order_id
FROM customers as C
    LEFT OUTER JOIN orders as O
        ON C.customer_id = O.customer_id

This query will return at least one row for every customer.
In those cases where a customer has made no orders yet, there will be a record in the result set with an empty (NULL) O.order_id.
Basically: the result set will contain as many records per customer as (s)he made orders + one record per customer that has made no orders.

a right join will bring orders that have no customer id in the customer master.

True if the query is

SELECT C.customer_id
           O.order_id
FROM customers as C
    RIGHT OUTER JOIN orders as O
        ON C.customer_id = O.customer_id

A non-empty result set in this case would (normally) indicate a data quality issue.

Cross join is the same as full outer join.

Not correct.

A CROSS JOIN produces a cartesian product between two tables, returning all possible combinations of all rows. It has no "ON" clause because you're joining everything to everything.

A FULL OUTER JOIN is a combination of a LEFT OUTER JOIN and a RIGHT OUTER JOIN. It returns all rows in both tables that match the query's ON clause, and in cases where the ON condition can't be satisfied for those rows it puts NULL values in the unpopulated fields.

(I could not have worded this any better, see SQL Server: What is the difference between CROSS JOIN and FULL OUTER JOIN? - Stack Overflow)

The number of records in the result set of a CROSS JOIN is predictable: the CROSS JOIN of a 10 records table and a a 50 records table will give 10 * 50 = 500 records.

The number of records in the result set of a FULL OUTER JOIN is not predictable: a 10 records table and a a 50 records table will give
If no records match the ON clause: 10 * 50 = 500 records. (same as CROSS JOIN)
If all records match the ON clause: 50 records. (same as INNER JOIN) (10 customers who have all made at least one order).

The result set will contain anything between 50 to 500 records.

3

Almost. An inner join means you get only the rows that match the join criteria, which may or not be =. Non-equal comparisons for an inner join may be rare, but they are valid.

4

When you talk about outer joins, you must be very clear about the order in which the tables are being joined. A left outer join from customer to orders is quite different from a loj from orders to customers.

Thus:

A left outer join, from orders to customers, will bring also orders rows that do not have a customer_id match from the customer so if customer 002 has no orders, it will bring one row from left side.

5

Thank you love to you all, Interview went well enough for the next round. Can't ask for more.

6

Success!!