0
SELECT * FROM left_table OUTER JOIN right_table on left_table.name = righ
t_table.name;

SELECT * FROM left_table FULL OUTER JOIN right_table on left_table.name =
 right_table.name;

These 2 statements gives below error. Are they not valid mysql statements ?

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'OUTER
 JOIN right_table on left_table.name = right_table.name' at line 1


mysql> SELECT * FROM left_table RIGHT OUTER JOIN right_table on left_table.name
= right_table.name;

and 

mysql> SELECT * FROM left_table RIGHT JOIN right_table on left_table.name
= right_table.name;

give same result. So whats the basic use of adding OUTER in statement?

I want to know what special purpose does it serve by adding OUTER in statement where as without adding it also its returning same output.

---- Update ----

Ok i give my table structure 

mysql> select * from left_table;
+----+---------+
| id | name    |
+----+---------+
|  1 | Pirate  |
|  2 | money   |
|  5 | Ninja   |
|  6 | pradeep |
+----+---------+

and 

mysql> select * from right_table;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | Rutabaga    |
|  2 | Pirate      |
|  3 | Darth Vader |
|  4 | Ninja       |
+----+-------------+

I ran statements like 

mysql> SELECT * FROM left_table LEFT JOIN right_table on left_table.name = right
_table.name where right_table.id is NULL;
+----+---------+------+------+
| id | name    | id   | name |
+----+---------+------+------+
|  2 | money   | NULL | NULL |
|  6 | pradeep | NULL | NULL |
+----+---------+------+------+

and 

mysql> SELECT * FROM left_table LEFT OUTER JOIN right_table on left_table.name =
 right_table.name where right_table.id is NULL;
+----+---------+------+------+
| id | name    | id   | name |
+----+---------+------+------+
|  2 | money   | NULL | NULL |
|  6 | pradeep | NULL | NULL |
+----+---------+------+------+

They still produce the same output. SO if any1 can show some statement where the results will differ. i would be able to understand it.

Hacker
  • 7,798
  • 19
  • 84
  • 154

2 Answers2

3

I dont know which version of mySql you are using but outer joins are used in combination with left or right, here is the syntax

table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

so outer join has no meaning in itself unless its accompanied by left or right.

You may find this reference handy

http://dev.mysql.com/doc/refman/5.0/en/join.html

user182630
  • 574
  • 3
  • 10
  • The question states in one of the examples that the OP is using it with a direction `...left_table RIGHT OUTER JOIN right_tabl...` – Ben Swinburne Nov 16 '11 at 14:42
  • The OP has written this SELECT * FROM left_table OUTER JOIN right_table on left_table.name = righ t_table.name; which is an outer join without a direction what am I missing – user182630 Nov 16 '11 at 14:47
  • @user182630 - I want to know what special purpose does it serve by adding OUTER in statement where as without adding it also its returning same output. – Hacker Nov 16 '11 at 14:49
  • The first two are MSSQL queries, hence the FULL OUTER JOIN. The OP then describes two MySQL queries below which render the same output and asks what the benefit of adding OUTER to the query is. – Ben Swinburne Nov 16 '11 at 14:49
  • @Ben Swinburne - all are mysql statements. To understand the concept well i tried out all statements. – Hacker Nov 16 '11 at 14:56
  • @Pradeep the special purpose it serves is as follows •The { OJ ... LEFT OUTER JOIN ...} syntax shown in the join syntax description exists only for compatibility with ODBC. The curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions. An e.g. SELECT left_tbl.* FROM { OJ left_tbl LEFT OUTER JOIN right_tbl ON left_tbl.id = right_tbl.id } WHERE right_tbl.id IS NULL; This is not same as in sQl – user182630 Nov 16 '11 at 14:57
  • Basically when should we use keyword OUTER is my main question – Hacker Nov 16 '11 at 15:13
  • A left outer join is a specialized outer join. Like an inner join, an outer join combines (joins) matching rows that are stored in two different tables. In addition, an outer join also adds unmatched rows from a reference table to the result set. In case of a left outer join this means that when there is a row in the left table which can’t be combined with any row in the right table (according to the join condition), MySQL… – user182630 Nov 16 '11 at 15:16
  • Have a look at this example..where humans is missing and comes in because of outer join. http://mysql-tips.blogspot.com/2005/04/mysql-outer-join-sql-tutorialexamples.html – user182630 Nov 16 '11 at 15:38
  • @user182630 - I saw the example. But see that he uses only LEFT JOIN and not LEFT OUTER JOIN. So can i take that LEFT JOIN and LEFT OUTER JOIN and difference is that OUTER provides only ODBC feature. – Hacker Nov 16 '11 at 16:01
1

They may, in your specific testing case, produce the same results. However, they will not always.

An outer join, instead of an inner join, means still show results from the main table (which depends on whether you're using left or right outer join) even if there is no corresponding row in the secondary table.

An inner join would not show the result at all if there was a row in the main table that matched the WHERE conditions but did not have a row that it could join with in the secondary table.

If your tables that you're joining in testing have matching rows for every result (i.e. every row in left_table has a row in right_table with the same value in the name field), then you won't see any difference.

What would make a difference is if you had some rows in one table, or in each table, that didn't have a row in the other table.

For: select * from left_table left outer join right_table on...where... and there are some rows in the right_table that meet the conditionals (in the where) but don't have a row in the left_table, they would not show. But if there were rows in the left_table that meet the conditionals without a joining row in the right_table, they would still show.

Same goes reverse for RIGHT OUTER JOIN.

EDIT:

LEFT JOIN and LEFT OUTER JOIN are aliases of each other. They are the same thing. I would just use OUTER to clarify for your own sake, if you'd like. I prefer to always use OUTER or INNER just so I can look quickly and see what I did.

Steph Rose
  • 2,126
  • 3
  • 23
  • 35
  • you mean to say adding where clause will make a difference to result. Can you just tell me in simple words y and when we should add OUTER keyword in my statement. – Hacker Nov 16 '11 at 15:11
  • A left outer join is a specialized outer join. Like an inner join, an outer join combines (joins) matching rows that are stored in two different tables. In addition, an outer join also adds unmatched rows from a reference table to the result set. In case of a left outer join this means that when there is a row in the left table which can’t be combined with any row in the right table (according to the join condition), MySQL… – user182630 Nov 16 '11 at 15:16
  • 1
    `left join` and `left outer join` are aliases of each other. They are the same thing. I would just use `OUTER` to clarify for your own sake, if you'd like. I prefer to always use `OUTER` or `INNER` just so I can look quickly and see what I did. – Steph Rose Nov 16 '11 at 16:01
  • @Stephanie - g8! This is what the answer that i needed. And yeah as manual says OUTER provides ODBC connectivity. – Hacker Nov 16 '11 at 16:04
  • Lastly, `OUTER` as the @user182630 said above, cannot be used alone. `FULL OUTER JOIN` is valid in some SQL languages, but is not available in MySQL. Which is why both of your first queries were erroring. – Steph Rose Nov 16 '11 at 16:18
  • @Stephanie - please add ur last but one comment to you answer. – Hacker Nov 16 '11 at 16:35