Filtering data using a join is bad?

[csinquirer]

For example, I have the following tables:

animal
-----------------------
animal_id | animal_name
-----------------------

owners
-----------------------
owner_id | owner_name
-----------------------

owners_animals
--------------------
owner_id | animal_id
--------------------

I want to find the animals with no owners so I do the query:

select animal_name from (select * from animals) as a left join (select * from owners_animals) as o on (a.animal_id = o.animal_id) where owner_id is NULL

A null owner_id column means that the associated animal has no owner.

Is this way of filtering data using a join acceptable and safe? With the same schema, is there a better alternative to get the same result?

[pclfw]

Well how about
SELECT A.*
FROM OWNERS_ANIMALS OA,
ANIMAL A
WHERE A.ANIMAL_ID = OA.ANIMAL_ID
AND OA.OWNER_ID IS NULL

Should work

[samaru]

For example, I have the following tables:

animal
-----------------------
animal_id | animal_name
-----------------------

owners
-----------------------
owner_id | owner_name
-----------------------

owners_animals
--------------------
owner_id | animal_id
--------------------

I want to find the animals with no owners so I do the query:

select animal_name from (select * from animals) as a left join (select * from owners_animals) as o on (a.animal_id = o.animal_id) where owner_id is NULL

A null owner_id column means that the associated animal has no owner.

Is this way of filtering data using a join acceptable and safe? With the same schema, is there a better alternative to get the same result?

Derived queries, may not best way to this. You can do it with a simple join.

Let's set up the data first (assuming you're running SQL Server):

USE [MrSkittles]
GO
/****** Object:  Table [dbo].[owners_animals]    Script Date: 02/09/2010 18:15:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[owners_animals](
	[owner_id] [int] NULL,
	[animal_id] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[owners_animals] ([owner_id], [animal_id]) VALUES (1, 3)
INSERT [dbo].[owners_animals] ([owner_id], [animal_id]) VALUES (4, 2)
INSERT [dbo].[owners_animals] ([owner_id], [animal_id]) VALUES (3, 2)
INSERT [dbo].[owners_animals] ([owner_id], [animal_id]) VALUES (4, 5)
INSERT [dbo].[owners_animals] ([owner_id], [animal_id]) VALUES (2, 3)
INSERT [dbo].[owners_animals] ([owner_id], [animal_id]) VALUES (4, 4)
INSERT [dbo].[owners_animals] ([owner_id], [animal_id]) VALUES (5, 5)
/****** Object:  Table [dbo].[owner]    Script Date: 02/09/2010 18:15:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[owner](
	[owner_id] [int] NULL,
	[owner_name] [varchar](40) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[owner] ([owner_id], [owner_name]) VALUES (1, N'peter')
INSERT [dbo].[owner] ([owner_id], [owner_name]) VALUES (2, N'joe')
INSERT [dbo].[owner] ([owner_id], [owner_name]) VALUES (3, N'dan')
INSERT [dbo].[owner] ([owner_id], [owner_name]) VALUES (4, N'kate')
INSERT [dbo].[owner] ([owner_id], [owner_name]) VALUES (5, N'vance')
INSERT [dbo].[owner] ([owner_id], [owner_name]) VALUES (6, N'lindsay')
INSERT [dbo].[owner] ([owner_id], [owner_name]) VALUES (7, N'seth')
/****** Object:  Table [dbo].[animal]    Script Date: 02/09/2010 18:15:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[animal](
	[animal_id] [int] NULL,
	[animal_name] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[animal] ([animal_id], [animal_name]) VALUES (1, N'tiger')
INSERT [dbo].[animal] ([animal_id], [animal_name]) VALUES (2, N'bull')
INSERT [dbo].[animal] ([animal_id], [animal_name]) VALUES (3, N'cow')
INSERT [dbo].[animal] ([animal_id], [animal_name]) VALUES (4, N'bird')
INSERT [dbo].[animal] ([animal_id], [animal_name]) VALUES (5, N'elephant')
INSERT [dbo].[animal] ([animal_id], [animal_name]) VALUES (6, N'hawk')

Here's the query:

select  *
from    animal a
        INNER JOIN owners_animals oa on a.animal_id = oa.animal_id
        RIGHT OUTER JOIN owner o on o.owner_id = oa.owner_id