How to pull back data in SQL where a value from one column is matched in another column

SELECT @@Microsoft SQL Server Management Studio I'm looking to find in my data all examples of where a value in column B, such as "Apple" or "Cherry", is found in column C, and pull the original line of data for column B. Example data stack below.

Column A	Column B	Column C	Column D
1	Apple
2	Orange	Apple	Return
3	Plum
4	Apple	Apple	Receive
5	Banana
6	Cherry
7	Peach
8	Peach	Cherry	Receive
9	Banana
0	Cherry	Cherry	Return

Returned values expected:

Column A	Column B	Column C	Column D
1	Apple
2	Orange	Apple	Return
4	Apple	Apple	Receive
6	Cherry
8	Peach	Cherry	Receive
0	Cherry	Cherry	Return

I am fairly new to SQL and unsure if this is a lack of knowledge or a limitation of SQL data manipulation. I am also learning Python and if this needs to be manipulated in Python instead, I'll give it a shot.

Tried:

SELECT *

WHERE

([Column B] is not null or [Column B] like [Column C])

FROM [DF]

Attempted code 2:

SELECT concat([Column B],'-',[Column C])

FROM [DF]

where Column B like concat([Column B],'-',[Column C])

Solution

DECLARE @Table TABLE (ColumnA INT,  ColumnB VARCHAR(10), ColumnC VARCHAR(10), ColumnD VARCHAR(10));
INSERT INTO @Table (ColumnA, ColumnB, ColumnC, ColumnD) VALUES
(1, 'Apple  ', NULL, NULL), (2, 'Orange', 'Apple', 'Return'), (3, 'Plum', NULL, NULL), (4, 'Apple', 'Apple', 'Receive'),
(5, 'Banana', NULL, NULL), (6, 'Cherry', NULL, NULL), (7, 'Peach', NULL, NULL), (8, 'Peach', 'Cherry', 'Receive'),
(9, 'Banana', NULL, NULL), (0, 'Cherry', 'Cherry', 'Return');

It sounds like you're looking for a self-join, where you join a table back to itself:

SELECT *
  FROM @Table a
    INNER JOIN @Table b
      ON a.ColumnB = b.ColumnC;

ColumnA	ColumnB	ColumnC	ColumnD	ColumnA	ColumnB	ColumnC	ColumnD
4	Apple	Apple	Receive	2	Orange	Apple	Return
4	Apple	Apple	Receive	4	Apple	Apple	Receive
6	Cherry			8	Peach	Cherry	Receive
0	Cherry	Cherry	Return	8	Peach	Cherry	Receive
6	Cherry			0	Cherry	Cherry	Return
0	Cherry	Cherry	Return	0	Cherry	Cherry	Return