SQL Query From two Table satisfying a condition
You want to run a query like this (see db-fiddle here):

SELECT 
  st.student_name, 
  -- st.student_dept,  -- &lt;&lt;= uncomment for an insight into
  -- sm.semester_dept, -- &lt;&lt;= how the query works!
  sm.semester_name
FROM student st
JOIN semester sm
ON
  st.student_dept = sm.semester_dept
WHERE st.student_dept IN
(
  SELECT
    sm.semester_dept AS sem_dept
  FROM semester sm
  GROUP BY sm.semester_dept
  HAVING COUNT(sm.semester_dept) = 1 
)


Result:

Student name  Semester name
___________________________
         Bob         Summer
      Briyan         Winter
       James         Summer


This query will work (I believe) for any/all of the major RDBMS servers - works on MySQL and (my reference implementation, PostgreSQL here). Oh, yes, just a point to note - the student_name field should be a PRIMARY KEY (or at least UNIQUE with NOT NULL) in order for this to be guaranteed to work properly!

This solution is much more elegant than my original query solution (here) - thanks to @ypercube for the hint!

============== DDL and DML ==================

CREATE TABLE student
(
  student_name VARCHAR (20) NOT NULL,
  student_dept VARCHAR (4) NOT NULL
);

INSERT INTO student VALUES
('Alex',   'CSE'),
('Bob' ,   'EEE'),
('Briyan', 'ME'),
('John',   'CSE'),
('James',  'ETE'),
('Mike',   'CE');

CREATE TABLE semester
(
  department VARCHAR (4) NOT NULL,
  semester   VARCHAR (6) NOT NULL
);

INSERT INTO semester VALUES
('CSE', 'Summer'),
('CSE', 'Winter'),
('EEE', 'Summer'),
('ETE', 'Summer'),
('ME',  'Winter'),
('CE',  'Summer'),
('CE',  'Winter');