SQL - FILTER condition in JOIN clause-CodePudding

I am inserting data to an already existing TableA and the following query is a part of a stored procedure. This part of the query inserts values into some columns of TableA. The stored procedure is very lengthy which has several insert statements to fill out the different columns in TableA.

INSERT INTO TableA (ID, Event, Date, Amount, Status_, Country)
    (SELECT DISTINCT ID, Event, Date, Amount, c.Status, b.Country 
     FROM TableA1 a
     JOIN TableB1 b ON b.employeeID = a.ID
     JOIN TableC1 c ON c.Status = ‘Active’)

Usually, a join condition consists of two columns, for example (a.Status_ = c.Status). But here, it's replaced with a filter condition (JOIN TableC1 c ON c.Status = ‘Active’).

The select query alone executes well and returns results. I'm trying to understand the effect made by this filter condition.

Can you explain, please?

Thanks

CodePudding user response：

It's the same as this...

SELECT DISTINCT
  ID, Event, Date, Amount, c.Status, b.Country 
FROM
(
    TableA1 a
  INNER JOIN
    TableB1 b
      ON b.employeeID = a.ID
)
CROSS JOIN
(
  SELECT * FROM TableC1 WHERE Status = 'Active'
)
  c

In effect, the INNER JOIN is resolved, and then each row from that is joined to every row from TableC1 WHERE Status = 'Active'

For example

TableA1

ID	Event	Date	Amount
1	e1	2022-01-01	11
2	e2	2022-02-02	22

TableB1

EmployeeID	Country
1	c1
2	c2

TableC1

some_id	Status
1	Sleeping
2	Active
3	Active
4	Sleeping

Would yield...

ID	Event	Date	Amount	Country	Status	(some_id, added by me)
1	e1	2022-01-01	11	c1	Active	2
1	e1	2022-01-01	11	c1	Active	3
2	e2	2022-02-02	22	c2	Active	2
2	e2	2022-02-02	22	c2	Active	3

You probably should have...

INNER JOIN
 TableC1 c
   ON  c.some_id = b.some_other_id
   AND c.Status  = 'Active'

CodePudding user response：

SELECT DISTINCT
  ID, Event, Date, Amount, c.Status, b.Country 
FROM
(
    TableA1 a
  INNER JOIN
    TableB1 b
      ON b.employeeID = a.ID
)
CROSS JOIN
(
  SELECT * FROM TableC1 WHERE Status = 'Active'
)
  c