0

I would like to show the columns of "linked" tables in SQL-Server database.
In my particular case, there are two tables "Loads" and "Locations", linked by Loads.LocationId=Locations.Id.

I just to work for a company, based on Oracle DB technology, which used the following notation (simple example):

DB_Name.show, Loads.Name, Locations.Name

Thas was called GCC or TOC, according to the version. As you see, the link between both tables is not mentioned.

I realise this is not common notation, and now I'm working for an SQL-Server based company, using basic SQL.
In order to show the same data, I need to write something like:

SELECT Loads.Name, Locations.Name FROM Loads, Locations
WHERE Loads.LocationId = Locations.Id

/* I know it's not a good idea to use a WHERE-clause for a join, */
/* but that's not the point here. */

As you can imagine, the tables are set up in such a way that the mentioned join is always applicable, so I'm wondering if there might be a way to configure my database in such a way that I don't always need to mention the WHERE (or JOIN clause), every time I write an SQL query.

Is this possible?
Thanks in advance

Improve this question
4
  • 2
    Are you asking how to define a view?
    – mustaccio
    May 11 at 14:35
  • @mustaccio. Does it make sense creating a view which spans over the entire DB (I mean, all linked tables)?
    – Dominique
    May 11 at 15:01
  • Well, if your goal is to avoid typing JOIN or WHERE then it does seem to make sense.
    – mustaccio
    May 11 at 16:04
  • ...Just be prepared that the performance can be terrible. Imagine a query that only need data from two tables, but uses the view and because of that now accesses 30 tables instead. (Yes, SQL Server can eliminate tables to access, but it isn't likely to happen in the end.)
    – Tibor Karaszi
    May 12 at 6:49

1 Answer 1

Reset to default
1

I'm afraid that there's no NATURAL JOIN or any other language element so you don't have to specify the join condition in MSSQL. (I wish that exists...)

As an aside, if you stick to the old join syntax, you won't be able to do OUTER JOINs in MSSQL. (I know you didn't ask for this, but thought I'd mention this part anyhow...)

Improve this answer
3
  • ... so, a "thing" like a stored procedure, creating all links so that, whenever an SQL query is launched, those links are taken into account? This does not exist? As far as my joins are concerned: don't worry I only work with inner joins.
    – Dominique
    May 11 at 14:20
  • 1
    Doesn't matter if you only use inner joins. You should DEFINITELY use the modern JOIN syntax if only to make your query explicitly clear and to make your code more easily understood (and less error-prone).
    – SMor
    May 11 at 15:01
  • No, nothing like that Dominique, I'm sorry. (There are no "links* in an RDBMS. There are foreign keys that tools might use to construct your join conditions on. But, again, no language construct in that area...)
    – Tibor Karaszi
    May 11 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.