いきなりですが、以下のようなテーブルから
①一つのチームにしか所属してない人はそのチームID
②複数のチームに所属している人はメインチームID
を取得するとします。

p_id	p_name	team_id	team_name	is_main_team
1	いちろう	1	チームA	True
1	いちろう	2	チームE	False
1	いちろう	3	チームY	False
2	よしお	1	チームA	False
2	よしお	3	チームE	True
3	ごろう	4	チームH	False
4	うえい	5	チーム！	False

色々なやりかたがあると思いますが、ぱっと思いつくのは
①、②それぞれについてselectを書き、unionするとかでしょうか。

それでも必要な結果は得られますが、複数回のテーブルアクセス等、
パフォーマンス面で無駄が発生します。

以下のSQLのように、CASE式と集約関数を組み合わせることで、
一つのselectで条件に沿ったデータの取得が行えます。

SQL

SELECT 
	p_id,p_name,
　　--一つのチームにのみ所属してる人
	CASE WHEN COUNT(*) = 1 THEN MAX(team_id)
    --複数チームに所属してる人
	ELSE
		MAX(
            CASE WHEN is_main_team = 1 THEN team_id
    		ELSE NULL 
            END
        )
	END AS main_team
FROM 
	test1
GROUP BY 
	p_id,p_name
ORDER BY
	p_id

実行結果

p_id	p_name	team_id
1	いちろう	1
2	よしお	3
3	ごろう	4
4	うえい	5

M根さん「いやそのまえに、このテーブルどうですの。」
M根さん「正規化もクソもあらへんやん。」
ワイ「ワイが来た時には既にこうでした。」