SQL Joining Question

SQL JOIN and different types of JOINs

What is SQL JOIN ?

SQL JOIN is a method to retrieve data from two or more database tables.

What are the different SQL JOINs ?

There are a total of five JOINs. They are :

  1. JOIN or INNER JOIN
  2. OUTER JOIN

     2.1 LEFT OUTER JOIN or LEFT JOIN
     2.2 RIGHT OUTER JOIN or RIGHT JOIN
     2.3 FULL OUTER JOIN or FULL JOIN

  3. NATURAL JOIN
  4. CROSS JOIN
  5. SELF JOIN

1. JOIN or INNER JOIN :

In this kind of a JOIN, we get all records that match the condition in both tables, and records in both tables that do not match are not reported.

In other words, INNER JOIN is based on the single fact that: ONLY the matching entries in BOTH the tables SHOULD be listed.

Note that a JOIN without any other JOIN keywords (like INNER, OUTER, LEFT, etc) is an INNER JOIN. In other words, JOIN is
a Syntactic sugar for INNER JOIN (see: Difference between JOIN and INNER JOIN).

2. OUTER JOIN :

OUTER JOIN retrieves

Either,
the matched rows from one table and all rows in the other table
Or,
all rows in all tables (it doesn't matter whether or not there is a match).

There are three kinds of Outer Join :

2.1 LEFT OUTER JOIN or LEFT JOIN

This join returns all the rows from the left table in conjunction with the matching rows from the
right table. If there are no columns matching in the right table, it returns NULL values.

2.2 RIGHT OUTER JOIN or RIGHT JOIN

This JOIN returns all the rows from the right table in conjunction with the matching rows from the
left table. If there are no columns matching in the left table, it returns NULL values.

2.3 FULL OUTER JOIN or FULL JOIN

This JOIN combines LEFT OUTER JOIN and RIGHT OUTER JOIN. It returns rows from either table when the conditions are met and returns NULL value when there is no match.

In other words, OUTER JOIN is based on the fact that: ONLY the matching entries in ONE OF the tables (RIGHT or LEFT) or BOTH of the tables(FULL) SHOULD be listed.

Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.

3. NATURAL JOIN :

It is based on the two conditions :

1. the JOIN is made on all the columns with the same name for equality.
2. Removes duplicate columns from the result.

This seems to be more of theoretical in nature and as a result (probably) most DBMS
don't even bother supporting this.

4. CROSS JOIN :

It is the Cartesian product of the two tables involved. The result of a CROSS JOIN will not make sense
in most of the situations. Moreover, we won't need this at all (or needs the least, to be precise).

5. SELF JOIN :

It is not a different form of JOIN, rather it is a JOIN (INNER, OUTER, etc) of a table to itself.

JOINs based on Operators

Depending on the operator used for a JOIN clause, there can be two types of JOINs. They are

1. Equi JOIN
2. Theta JOIN

1. Equi JOIN :

For whatever JOIN type (INNER, OUTER, etc), if we use ONLY the equality operator (=), then we say that
the JOIN is an EQUI JOIN.

2. Theta JOIN :

This is same as EQUI JOIN but it allows all other operators like >, <, >= etc.

Many consider both EQUI JOIN and Theta JOIN similar to INNER, OUTER
etc JOINs. But I strongly believe that its a mistake and makes the
ideas vague. Because INNER JOIN, OUTER JOIN etc are all connected with
the tables and their data whereas EQUI JOIN and THETA JOIN are only
connected with the operators we use in the former.

Again, there are many who consider NATURAL JOIN as some sort of
"peculiar" EQUI JOIN. In fact, it is true, because of the first
condition I mentioned for NATURAL JOIN. However, we don't have to
restrict that simply to NATURAL JOINs alone. INNER JOINs, OUTER JOINs
etc could be an EQUI JOIN too.

SQL join problem

You need a LEFT JOIN WHERE IS NULL query (aka outer join):

SELECT table1.*
FROM table1
LEFT OUTER JOIN table2 
   ON table1.id = table2.id
WHERE table2.id IS NULL

Or a NOT IN:

SELECT *
FROM table1
WHERE id NOT IN (SELECT id FROM table2)

SQL join one time only

Are you looking to join on id?

select 
    t1.id, 
    t1.sum, 
    case when t1.sum = t2.sum then 'Match' else 'Mismatch' end as result
from table1 t1
inner join table2 t2 on t2.id = t1.id

Alternatively, if ids do not math, you might want to use row_number():

select 
    t1.id id1, 
    t1.sum sum1, 
    t2.id id2,
    t2.sum sum2,
    case when t1.sum = t2.sum then 'Match' else 'Mismatch' end as result
from (select t1.*, row_number() over(order by id) rn from table1 t1) t1
inner join ((select t2.*, row_number() over(order by id) rn from table2 t2) t2 
    on t2.rn = t1.rn

Finally: if the count of rows in both table might be different, use a full join instead of an inner join (if your database supports it):

select 
    t1.id id1, 
    t1.sum sum1, 
    t2.id id2,
    t2.sum sum2,
    case when t1.sum = t2.sum then 'Match' else 'Mismatch' end as result
from (select t1.*, row_number() over(order by id) rn from table1 t1) t1
full join (select t2.*, row_number() over(order by id) rn from table2 t2) t2 
    on t2.rn = t1.rn

SQL JOIN - WHERE clause vs. ON clause

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345.

The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

SQL Join Problems

join argus.MINOR_ITEM_CLASS mi 
    ON (i.minor_item_class = mi.minor_item_class)
join argus.MAJOR_ITEM_CLASS mj 
    ON (mi.major_item_class = mj.major_item_class)

My guess is that your error resides in one of these 2 joins. When you only use the word JOIN it assumes that you are trying to do an INNER JOIN which returns all records that have at least 1 to 1. I don't know what your data looks like but I am assuming that there is a many to many relationship between minor item class and major item class so when you run this query you are receiving duplicated records for almost every field, but the major item class differs.

I would look at the results. Most of the columns will have repeating data that doesn't change while one of the columns will have a different value for every row. That should tell you that the column with differing data for each row is the column that you should be joining differently.

Otherwise, I would say that your query is formatted correctly.

sql join clause position question

Let's rewrite SQLs:

select t1.id, t2.col1
from table1 t1
left join table2 t2 on t2.col1=t1.id and t2.col2='xxx'
where t1.col2='xxx'

---

select t1.id, t2.col1
from table1 t1
left join table2 t2 on t2.col1=t1.id
where t1.col2='xxx' and t2.col2='xxx'

Fill the table with data

t1 (id, col2):

1 xxx

2 xxx

3 jjj

t2 (col1, col2):

1 uuu

2 xxx

3 xxx

Result of first query:

1 NULL

2 2

Result of second query:

2 2

So, if you put any conditions in 'join', joining line may not be joined and result of join will be null.

See this link to understand

Tricky SQL queries: joining, grouping, having

This should give you what you want for number 1, for number 2 I would need the data in a sqlfiddle to play around with. In the meantime, I would suggest looking at lag although some combination of first and last might give you what you want. *Note this will not give you the EXACT answer, it is meant to be a reference.

select mov.title, sum(rat.stars), max(rat.stars), rev.name
from Movie mov,
  Rating rat,
  Reviewer rev
where mov.mid = rat.mid
and rat.rid = rev.rid
group by mov.title;

---

Related Topics