-4

Is it possible (ex. in MS SQL) to perform Join in way like this:

select p.* from Person p join Order o

By default the DB engine could look for any relation between this tables and use it without writing additional: 

on p.ID = o.FK_Person
Pavel
  • 431
  • 1
  • 6
  • 17
  • Not sure what you're asking. When you join you have to join the tables on some field. Doesn't have to be PK or FK but will be most of the time. – Rick S Jun 18 '15 at 19:47
  • I mean - all the time I'm writing the similar queries. In programming stuff you have so many things which let you write less - and here you usualy join by PK and FK so why be default it wouldn't use PK and FK if nothing specified/ – Pavel Jun 18 '15 at 19:50
  • 3
    Because programming isn't magic. You have to know what you're doing. – Rick S Jun 18 '15 at 19:51
  • You see what you're doing - you see the db chart - you have relation between the tables or not. I was just suprised that this basic and very frequent operation requires writing the same code all the time if you could just say: 'link those tables for me (you know how to do it)' – Pavel Jun 18 '15 at 20:07
  • There's more than one way to link tables together. How do you propose that SQL do it unless you tell it? You want it to guess and give you wrong answer? – Rick S Jun 18 '15 at 20:11
  • It could be done in this way: if there is one way to link then - use it, otherwise - throw error (this could be just semantic shortcut) Anyway my question makes no big sence at this moment because this just doesn't exist (no matter if this is possible or not) – Pavel Jun 18 '15 at 20:17
  • Call Microsoft and ask them to enhance their product for you. You're not going to get an answer here... – Rick S Jun 18 '15 at 20:20
  • Pavel - in anything but the simplest tables, there's always more than one way to join them. You might often want to join on columns that aren't any kind of keys, or that are keys to some third table that's not even involved in this query. Coding SQL to make the assumption you're suggesting - might save a couple of seconds' typing, but it would introduce hours of painful troubleshooting later on. – Mike K Jun 18 '15 at 20:23

2 Answers2

0

NO you need to specify the join clause in on of the two ways.

Implicit join notation:

SELECT p.* 
FROM Person p, Order o
WHERE p.ID = o.FK_Person

Or explicit join notation:

SELECT p.* 
FROM Person p
INNER JOIN Order o
        ON p.ID = o.FK_Person

If you won't specify any join order, no server would join anything. It's defined in the SQL Standard.

Ionic
  • 3,884
  • 1
  • 12
  • 33
  • 1
    Technically speaking what you call an implicit join is a cross join. The reason it works is because of the where predicate. – Sean Lange Jun 18 '15 at 19:48
  • @SeanLange The condition makes it an inner join, though. Check out this post for example: http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins – Fabian Bigler Jun 18 '15 at 19:49
  • @SeanLange every join is in the first case a cartesian product (cross join for you). After that a filter is applied (inner join) and maybe the preserved outer rows will be attached back (left, right, full join for example). The Plan Optimizer in SQL Server will execute both queries completely the same. Just the notation is the difference. – Ionic Jun 18 '15 at 19:51
  • @FabianBigler right, it works because of the where predicate which filters out the nonmatching rows. – Sean Lange Jun 18 '15 at 19:51
  • @Ionic if you change the comma separator to "cross join" and keep the where clause it is the same thing. By default, when sql server encounters multiple tables with a comma separator it is just short hand for the explicit cross join. – Sean Lange Jun 18 '15 at 19:53
  • @SeanLange as every `INNER JOIN` would do too on the `ON` clause. Just as microsoft itself describes it. ;-) Well no, it's not the shorthand. It's the implicit notation. But anyway, this won't help the OP, as it wasn't it's question. The core point is already answered. :-) – Ionic Jun 18 '15 at 19:53
0

Yes ,, possible when using non ansi joins comma seprated like 

select p.* from Person p , Order o

warnings : result will be Cartesian product of two tables .ANSI Joins are not possible.

Thanks

Rohit
  • 172
  • 1
  • 9
  • This is a cross join. – Sean Lange Jun 18 '15 at 19:49
  • Well this will join, yes but it will be a cartesian join as you already stated, not a real join. This will produce much overhead. :-D – Ionic Jun 18 '15 at 19:49
  • @SeanLange he already stated it as cartesian product in his original post. A cartesian product is a cross join. ;-) – Ionic Jun 18 '15 at 19:49
  • 3
    Semantics aside :-) This is bad and will not do what the OP is asking for... which is a magical relationship join between the two tables – TheMadDBA Jun 18 '15 at 19:51
  • 1
    @TheMadDBA right. But I never heard about that in any database system. And even if it's possible, it has too many side effects. I won't use it, even if it's possible. ;-) – Ionic Jun 18 '15 at 19:53
  • yes.. @Ionic this will produce much overhead .. and result set may be not useful and redundant .it will be good if requirement given clearly.. – Rohit Jun 18 '15 at 19:53
  • Yeah the overhead of looking through the metadata would be awful for performance. – Sean Lange Jun 18 '15 at 19:54
  • I know of a few relational databases that allow syntax like EACH Order OF Person... but not any SQL based ones. And you are correct... it causes problems. – TheMadDBA Jun 18 '15 at 19:54