Conditional join on tables
<p>I want to create a conditional join on tables.</p>

<p>I already checked <a href="https://stackoverflow.com/questions/26715656/sql-conditional-join-on-different-tables">here</a>. But I also read that an <code>OUTER JOIN</code> might not be the best way (performance wise) since a full table scan still takes place? And there also seems to be difference in opinion on the performance of using <code>UNION</code>.</p>

<p>What is the way I can conditionally join tables based on my requirements below?
ps. If your solution requires it, I can implement this as a stored procedure too to create some more dynamic SQL.</p>

<p>For all rows where userfavorites.objecttype=100, based on the value of <code>photos.objecttype</code> I want to join another table.
If the <code>objecttype</code> value is 1 I want to join <code>[locations]</code>, if the value is 2 I want to join <code>[persons]</code>.</p>

<p>As output I want the following columns:<br>
photos.title,photos.locpath, (persons.title or locations.title), (persons.friendlyurl or locations.friendlyurl),userfavorites.objectid,userfavorites.objecttype</p>

<p>I now have this to get the rows for a single user:</p>

<pre><code>SELECT * FROM userfavorites uf
INNER JOIN photos vp ON uf.objectid=vp.id
WHERE uf.objecttype=100 AND uf.userid='32DD30EB-1691-457B-9FF5-FC41D687E579'
</code></pre>

<p>DDL and insert statements:</p>

<pre><code>CREATE TABLE [dbo].[photos](
    [id] [int] NOT NULL,
    [objectid] [int] NOT NULL,
    [locpath] [nvarchar](150) NOT NULL,
    [objecttype] [tinyint] NOT NULL,
    [title] [nvarchar](50) NULL) ON [PRIMARY]

CREATE TABLE [dbo].[userfavorites](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [userid] [uniqueidentifier] NOT NULL,
    [objectid] [int] NOT NULL,
    [objecttype] [tinyint] NOT NULL CONSTRAINT [DF_userfavorites_objecttype]  DEFAULT ((0)),
 CONSTRAINT [PK_userfavorites] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[persons](
    [id] [int] NOT NULL,
    [title] [nvarchar](80) NOT NULL,
    [friendlyurl] [nvarchar](80) NULL,
 CONSTRAINT [PK_persons_1] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[locations](
    [id] [int] NOT NULL,
    [title] [nvarchar](80) NOT NULL,
    [friendlyurl] [nvarchar](80) NULL,
 CONSTRAINT [PK_locations_1] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]




USE [tt]
SET IDENTITY_INSERT [dbo].[userfavorites] ON 

INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'32dd30eb-1691-457b-9ff5-fc41d687e579', 41029, 100)
INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'32dd30eb-1691-457b-9ff5-fc41d687e579', 41030, 100)
INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'32dd30eb-1691-457b-9ff5-fc41d687e579', 40880, 100)
INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'32dd30eb-1691-457b-9ff5-fc41d687e579', 40885, 100)
INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'32dd30eb-1691-457b-9ff5-fc41d687e579', 40882, 100)
INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'32dd30eb-1691-457b-9ff5-fc41d687e579', 4067, 100)
INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'BC5EB8A7-FEC2-4932-9C67-AE5A35C4012B', 4067, 100)
SET IDENTITY_INSERT [dbo].[userfavorites] OFF

INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (41029, 1, 1, N'hiltonsky.jpg', N'Overview')
INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (41030, 1, 1, N'pool.jpg', N'Swimming')
INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (40880, 2, 1, N'entrance.jpg', N'Lobby Entrance')
INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (40885, 2, 1, N'room.jpg', N'Room view')
INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (40882, 1, 2, N'zuck.jpg', N'Mark')
INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (4067, 2, 2, N'gates.jpg', N'Bill')
INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (50673, 3, 2, N'musk.jpg', N'Elon')

INSERT [dbo].[locations] ([id], [title], [friendlyurl]) VALUES (1, N'Hilton Hotel', 'hilton-hotel')
INSERT [dbo].[locations] ([id], [title], [friendlyurl]) VALUES (2, N'Marriot Hotel', 'marriot-hotel')


INSERT [dbo].[persons] ([id], [title], [friendlyurl]) VALUES (1, N'Mark Zuckerberg', 'mark-zuckerberg')
INSERT [dbo].[persons] ([id], [title], [friendlyurl]) VALUES (2, N'Bill Gates', 'bill-gates')
INSERT [dbo].[persons] ([id], [title], [friendlyurl]) VALUES (3, N'Elon Musk', 'elon-musk')
</code></pre>

<p>So for userid <code>32dd30eb-1691-457b-9ff5-fc41d687e579</code>, the outcome would be:</p>

<pre><code>+----------------+---------------+-----------------+-----------------+----------+------------+
|   phototitle   |    locpath    |      title      |   friendlyurl   | objectid | objecttype |
+----------------+---------------+-----------------+-----------------+----------+------------+
| Overview       | hiltonsky.jpg | Hilton Hotel    | hilton-hotel    |        1 |          1 |
| Swimming       | pool.jpg      | Hilton Hotel    | hilton-hotel    |        1 |          1 |
| Lobby Entrance | entrance.jpg  | Marriot Hotel   | marriot-hotel   |        2 |          1 |
| Room view      | room.jpg      | Marriot Hotel   | marriot-hotel   |        2 |          1 |
| Mark           | zuck.jpg      | Mark Zuckerberg | mark-zuckerberg |        1 |          2 |
| Bill           | gates.jpg     | Bill Gates      | bill-gates      |        2 |          2 |
+----------------+---------------+-----------------+-----------------+----------+------------+
</code></pre>