join 2 tables on hardcoded value

Question

I don't understand quite how do some parts of the 'join' work.

I have 2 tables that I need to join on some columns.

Lets say the following relations:

TabA ( IDA, IDB, Label, isActive)

TabB (IDB, INFORMATION)

Here we join them on IDB. But what if we want to join onisActive = 1?

What will happen?

Select * 
  from TabA
  left join TabB 
    on TabA.IDB = TabB.IDB 
    and isActive = 1

I get some kind of cartesian product but I don't understand why nor what logic?

Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it, your current attempt and your desired results. For more details, [read this.](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) — Zohar Peled, Apr 02 '19 at 14:03
I don't see anything wrong with your query. What is it about the results that you don't understand? — Tab Alleman, Apr 02 '19 at 14:08
`isactive = 1` is a filter for TABA's rows, it's not a join predicate so you join all rows from TABB to each row from that filtered TABA — Denis Rubashkin, Apr 02 '19 at 14:08
I was incorrect a little in my previous comment: rows from `TABA` wouldn't filtered but recordset would have NULL value in TabB's columns for such rows (which don't meet the condition `isactive = 1`) — Denis Rubashkin, Apr 02 '19 at 14:37
Find a definition of left join and tell us what you think should happen & why & ask a question where you are stuck. Otherwise you are just asking, how does left join work, and that's an easily found multiply duplicate question. — philipxy, Apr 02 '19 at 17:58
Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Always know what inner join you want as part of a left join. — philipxy, Apr 03 '19 at 05:11
Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Always know what inner join you want as part of a left join. PS But what if we want to join on Tuesday? — philipxy, Apr 03 '19 at 05:37
Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — philipxy, Apr 03 '19 at 05:39

Shadiqur · Answer 1 · 2019-04-02T14:29:14.773

0

you can join two table where they have a common field like here you have IDB but isactive is not belong on the both table

you can use isactive in where clause:

Select TabA.* from TabA
left join TabB 
on TabA.IDB = TabB.IDB 
where TabA.isactive = 1

edited Apr 02 '19 at 14:29

answered Apr 02 '19 at 14:07

Shadiqur

490
1
5
18

. . This answer is simply wrong and you should understand why. – Gordon Linoff Apr 02 '19 at 14:19

score 0 · Answer 2 · answered Apr 02 '19 at 14:17

This query is an outer join:

Select *
from TabA a left join
     TabB b
     on a.IDB = b.IDB and b.isactive = 1;

This follows the same rules of any left join. All the rows in TabA are in the result set. In addition, any matches from TabB are included. In this case, only rows from TabB where isactive = 1 are included. If there is no row, then all matching columns are NULL.

For instance, if you have tabA as:

IDA     IDB
 1       1
 2       1
 3       2

And tabB as:

IDB    isactive
 1         0
 2         1

Then the result set is:

IDA     IDB    IDB    isActive
 1       1     NULL      NULL
 2       1     NULL      NULL
 3       2      2        1

`isActive` is a column of `TabA` – Denis Rubashkin Apr 02 '19 at 14:22 — Denis Rubashkin, Apr 02 '19 at 14:22

join 2 tables on hardcoded value

2 Answers2